Power BI Desktop DAX restartuje kolumnę sumy uruchomionej


9

Mam stolik, w którym każda osoba ma zapis na każdy dzień roku. Użyłem tej funkcji do osiągnięcia sumy bieżącej na podstawie kolumny salda dziennego

CALCULATE(
SUM(Leave[Daily Balance]),
FILTER(
   ALLEXCEPT(Leave, Leave[Employee Id]),
   Leave[Date] <= EARLIER(Leave[Date])
))

ale potrzebuję sumy bieżącej, aby zrestartować od 1, jeśli Typ = Działający ORAZ bieżąca suma dziennego salda jest mniejsza niż zero ORAZ Typ poprzedniego rzędu nie jest równy Działającej. Poniżej zrzut ekranu z Excela. Potrzebna jest kolumna funkcji.

wprowadź opis zdjęcia tutaj


1
W wierszu z 5 listopada, osoba 1, załóżmy, że nasze dane testowe były puste. Czy „wymagana funkcja” zwróci 1 lub 2 6 listopada?
Ryan B.

Zwróci 2 dla 6 listopada. „Reset” nie nastąpi, ponieważ 5 listopada będzie 1 (nie jest liczbą ujemną). Dziękuję za szczegółowy post. Dzisiaj recenzuję
LynseyC,

Odpowiedzi:


1

Jest to nie tylko suma bieżąca z warunkiem, ale także zagnieżdżona / klastrowana, ponieważ logika musi być zastosowana na poziomie identyfikatora. W przypadku dużych tabel M jest w tym lepszy niż DAX, ponieważ nie zużywa tyle pamięci RAM. (Napisałem o tym tutaj blog: Link do Blogpost

Poniższa funkcja dostosowuje tę logikę do bieżącego przypadku i musi być zastosowana na poziomie identyfikatora: (Wymagane nazwy kolumn to: „Typ”, „Dzienny limit”, „Dostosowania”)

(MyTable as table) => let SelectJustWhatsNeeded = Table.SelectColumns(MyTable,{"Type", "Daily Allowance", "Adjustments"}), ReplaceNulls = Table.ReplaceValue(SelectJustWhatsNeeded,null,0,Replacer.ReplaceValue,{"Adjustments"}), #"Merged Columns" = Table.CombineColumns(ReplaceNulls,{"Daily Allowance", "Adjustments"}, List.Sum,"Amount"), TransformToList = List.Buffer(Table.ToRecords(#"Merged Columns")), ConditionalRunningTotal = List.Skip(List.Generate( () => [Type = TransformToList{0}[Type], Result = 0, Counter = 0], each [Counter] <= List.Count(TransformToList), each [ Result = if TransformToList{[Counter]}[Type] = "working" and [Result] < 0 and [Type] <> "working" then TransformToList{[Counter]}[Amount] else TransformToList{[Counter]}[Amount] + [Result] , Type = TransformToList{[Counter]}[Type], Counter = [Counter] + 1 ], each [Result] )), Custom1 = Table.FromColumns( Table.ToColumns(MyTable) & {ConditionalRunningTotal}, Table.ColumnNames(MyTable) & {"Result"} ) in Custom1


To rozwiązało problem. Działa idealnie i nie spowolnił raportu. Dzięki
LynseyC,

5

Przegląd

Jest to trudna sprawa, o którą prosi PowerBI, więc uporządkowane podejście może być trudne do znalezienia.

Największym problemem jest to, że model danych PowerBI nie obsługuje koncepcji liczenia działającego - przynajmniej nie tak, jak robimy to w Excelu. W programie Excel kolumna może odwoływać się do wartości występujących w „poprzednim wierszu” tej samej kolumny, a następnie być dostosowywana przez „dzienną zmianę” wymienioną w innej kolumnie.

PowerBI może to naśladować, sumując wszystkie codzienne zmiany w niektórych podzbiorach wierszy. Bierzemy wartość daty w naszym bieżącym wierszu i tworzymy filtrowaną tabelę, w której wszystkie daty są mniejsze niż data bieżącego wiersza, a następnie podsumowujemy wszystkie codzienne zmiany z tego podzbioru. Może się to wydawać subtelną różnicą, ale jest dość znacząca:

Oznacza to, że nie ma możliwości „zastąpienia” naszej sumy bieżącej. Jedyną matematyką, która jest wykonywana, jest kolumna zawierająca codzienne zmiany - kolumna zawierająca „sumę bieżącą” jest tylko wynikiem - nigdy nie jest używana w obliczeniach kolejnych wierszy.

Musimy porzucić koncepcję „resetu” i zamiast tego wyobrazić sobie utworzenie kolumny zawierającej wartość „korekty”. Nasza korekta będzie wartością, którą można uwzględnić, aby po spełnieniu opisanych warunków suma dziennych sald i korekt wyniosła 1.

Jeśli spojrzymy na obliczone wyniki podane przez OP, zobaczymy, że wartość naszej sumy bieżącej w dniu „niepracującym” tuż przed dniem „roboczym” daje nam potrzebną kwotę, która, jeśli zostanie odwrócona, wyniesie zero i spowodować, że suma bieżąca w każdym kolejnym dniu roboczym wzrośnie o jeden. To jest nasze pożądane zachowanie (z jednym problemem, który zostanie opisany później).

Wynik

wprowadź opis zdjęcia tutaj

Most Recent Date Prior to Work = 

CALCULATE(
Max(Leave[Date]),
FILTER(
   ALLEXCEPT(Leave, Leave[Id]),
   Leave[Date] = EARLIER(Leave[Date]) -1 && Leave[Type] <> "Working" && Earlier(Leave[Type]) = "Working"
))

Pomaga poznać różnicę między kontekstami wierszy i filtrów oraz sposób działania EARLIER w celu wykonania tego obliczenia. W tym scenariuszu możesz myśleć o „WCZEŚNIEJ” jako oznaczającym, że „to odniesienie wskazuje na wartość w bieżącym wierszu”, a w innym przypadku odniesienie wskazuje na całą tabelę zwróconą przez „ALLEXCEPT (Leave, Leave [Id]).” W tym w ten sposób znajdujemy miejsca, w których bieżący wiersz ma typ „Pracujący”, a wiersz z poprzedniego dnia ma inny typ.

Most Recent Date Prior to Work Complete = 

CALCULATE(
Max(Leave[Most Recent Date Prior to Work]),
FILTER(
   ALLEXCEPT(Leave, Leave[Id]),
   Leave[Date] <= EARLIER(Leave[Date])
))

Obliczenia imitują operację typu „wypełnij”. Mówi: „Patrząc na wszystkie wiersze, których data jest wcześniejsza niż data W TYM wierszu, zwróć największą wartość w polu„ Ostatnia data przed pracą ”.

Daily Balance Adjustment = 

CALCULATE(
SUM(Leave[Running Daily Balance]),
FILTER(
   ALLEXCEPT(Leave, Leave[Id]),
   Leave[Date] = EARLIER(Leave[Most Recent Date Prior to Work Complete])
))

Teraz, gdy w każdym wierszu znajduje się pole wyjaśniające, gdzie znaleźć saldo dzienne, które można wykorzystać jako naszą korektę, możemy po prostu spojrzeć w górę ze stołu.

Adjusted Daily Balance = Leave[Running Daily Balance] - Leave[Daily Balance Adjustment]

I na koniec dostosowujemy naszą sumę bieżącą dla ostatecznego wyniku.

Problem

Podejście to nie rozwiązuje problemu zliczania, o ile bieżące saldo dzienne nie spadnie poniżej zera. Wcześniej udowodniono, że się mylę, ale powiedziałbym, że nie można tego osiągnąć w samym języku DAX, ponieważ powoduje to zależność cykliczną. Zasadniczo stawiasz wymaganie: użyj wartości zagregowanej, aby określić, co powinno zostać uwzględnione w agregacji.

To tyle, ile mogę ci przynieść. Mam nadzieję, że to pomoże.


1
Jeśli chodzi o twój ostatni punkt, uważam, że masz rację. Język DAX nie może wykonywać rekurencji.
Alexis Olson

3

Mam nadzieję, że następnym razem wkleisz plik csv lub kod, który generuje przykładowe dane zamiast obrazu. :)

Pozwól, że zasugeruję Ci wykonanie obliczeń w PowerQuery. Próbowałem podzielić kod na kilka kroków, aby poprawić czytelność. Może to wyglądać na nieco bardziej złożone, jednak działa dobrze. Wystarczy wkleić go w zaawansowanym edytorze, a następnie zastąpić źródło danymi źródłowymi. Powodzenia!

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUMzDSMzIwtFTSUQpILSrOz1MwBDLL84uyM/PSlWJ1gGqMsKuBSBrjkzQhwnRTItSYEaHGHJ9DLPBJWhI23dAAjwGGOAIRIokj9OCmxwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [date = _t, name = _t, #"type" = _t]),
    SetTypes = Table.TransformColumnTypes(Source,{{"date", type date}, {"name", type text}, {"type", type text}}),
    TempColumn1 = Table.AddColumn(SetTypes, "LastOtherType", (row)=>List.Max(Table.SelectRows(SetTypes, each ([name] = row[name] and [type] <> row[type] and [date] <= row[date]))[date], row[date]), type date) //Here for each row we select all rows of other type with earlier date, and take max that date. Thus we know when was previous change from one type to another
 //Here for each row we select all rows of other type with earlier date, and take max that date. Thus we know when was previous change from one type to another
,
    TempColumn2 = Table.AddColumn(TempColumn1, "Count", (row)=>
(if row[type]="working" then 1 else -1) * 
Table.RowCount(
Table.SelectRows(SetTypes, each ([name] = row[name] and [type] = row[type] and [date] <= row[date] and [date] > row[LastOtherType])) /* select all rows between type change (see prev step) and current row */
), /*and count them*/
Int64.Type) // finally multiply -1 if they are not working type
,
    FinalColumn = Table.AddColumn(TempColumn2, "FinalFormula", (row)=> 
(if row[type] = "working" then row[Count] else /* for working days use Count, for others take prev max Count and add current Count, which is negative for non-working*/
Table.LastN(Table.SelectRows(TempColumn2, each [name] = row[name] and [type] = "working" and [LastOtherType] <= row[LastOtherType]),1)[Count]{0}
+ row[Count])
, Int64.Type),
    RemovedTempColumns = Table.RemoveColumns(FinalColumn,{"LastOtherType", "Count"})
in
    RemovedTempColumns

Nie jestem pewien, czy obejmuje to każdy scenariusz, ale wydaje się, że to właściwe podejście.
Mike Honey

Mogę to uruchomić tylko wtedy, gdy pierwszym typem dla każdej osoby jest Działający. Podobnie jak w przykładach języka DAX, ponownie uruchamia numerację dla ruchu roboczego, gdy skumulowana suma dla poprzedniego wiersza jest liczbą dodatnią. Wydaje mi się, że moje zdjęcie było mylące, ponieważ zawierało tylko ten scenariusz. Powinienem uwzględnić czas, w którym typ zmienił się na działający, ale poprzednia suma wierszy była dodatnia.
LynseyC

@LynseyC cóż, ten kod nie jest oczywiście doskonałym i kompletnym rozwiązaniem, ale raczej przykładem metod, które można zastosować. Po prostu zmodyfikuj, jeśli dla twojego scenariusza.
Eugene

@LynseyC również jedną z zalet wykonywania tej matematyki w programie PowerQuery zamiast w języku DAX jest łatwy sposób na uniknięcie tymczasowych kolumn modelu danych.
Eugene

3

Myślę, że mam!

Oto wynik, bazując na rozwiązaniu, które opublikowałem wcześniej: (Dane zostały zmodyfikowane, aby pokazać więcej zachowań „praca / brak pracy” i przypadki użycia)

WYNIK

wprowadź opis zdjęcia tutaj

DETALE

(1) Zrzuć kolumny „Skorygowane saldo dzienne” i „Korekta dziennego salda”. Za chwilę uzyskamy ten sam wynik o jeden krok mniej.

(2) Utwórz następującą kolumnę (RDB = „bieżące saldo dzienne”) ...

Grouped RDB = 

CALCULATE(
SUM(Leave[Daily Balance]),
FILTER(
   ALLEXCEPT(Leave, Leave[Id], Leave[Most Recent Date Prior to Work Complete]),
   Leave[Date] <= EARLIER(Leave[Date]) 
))

Po utworzeniu „Ostatniej daty przed zakończeniem pracy” mamy w rzeczywistości element niezbędny do wykonania „resetu”, który, jak twierdziłem, był wcześniej niemożliwy. Filtrując to pole, mamy możliwość rozpoczęcia każdego wycinka od „1”

(3) Nadal mamy ten sam problem, nie możemy spojrzeć na wynik w naszej kolumnie i użyć go do podjęcia decyzji, co zrobić później w tej samej kolumnie. Ale MOŻEMY zbudować nową kolumnę dostosowania, która będzie przechowywać te informacje! Mamy już odniesienie do „Ostatniej daty przed pracą” - to ostatni dzień w poprzedniej grupie ... wiersz z potrzebnymi informacjami!

Grouped RDB Adjustment = 

VAR CalculatedAdjustment =
CALCULATE(
SUM(Leave[Grouped RDB]),
FILTER(
   ALLEXCEPT(Leave, Leave[Id]),
   Leave[Date] IN SELECTCOLUMNS(
        FILTER(
            Leave,
            Leave[Most Recent Date Prior to Work] <> BLANK() &&
            Leave[id] = EARLIER(Leave[Id])), "MRDPtW", Leave[Most Recent Date Prior to Work]) &&
   Leave[Most Recent Date Prior to Work Complete] < EARLIER(Leave[Most Recent Date Prior to Work Complete]) &&
   Leave[Most Recent Date Prior to Work Complete] <> Blank()
))

RETURN if (CalculatedAdjustment > 0, CalculatedAdjustment, 0)

Więc patrzymy na ostatni dzień w każdej poprzedniej grupie i jeśli całkowita suma tych korekt ma wartość dodatnią, stosujemy ją, a jeśli jest ujemna, zamiast tego pozostawiamy ją w spokoju. Ponadto, jeśli pierwsze dni naszej osoby są dniami wolnymi od pracy, nie chcemy wcale tego początkowego ujemnego bitu w naszym dostosowaniu, więc również zostanie odfiltrowane.

(4) Ten ostatni krok doprowadzi dostosowanie do ostatecznego wyniku. Zsumuj dwie nowe kolumny i w końcu powinniśmy mieć nasz Skorygowany dzienny bilans salda. Voila!

Adjusted Running Daily Balance = Leave[Grouped RDB] + Leave[Grouped RDB Adjustment]

Po drodze do tego wyniku zbudowaliśmy wiele dodatkowych kolumn, co zwykle nie jest moją ulubioną rzeczą. Ale to było trudne.


Cześć @ Ryan B. Działa to doskonale dla ponad 200 osób w mojej organizacji, ale jedna nie działa. Sam próbowałem zmienić kod, ale nie mogę uzyskać niczego, aby rozwiązać problem. Myślę, że dzieje się tak, ponieważ pracowali długo, a potem pracowali zaledwie jeden dzień, zanim mieli więcej wolnego. Połączyłem się z obrazem, aby pokazać problem. Dzięki Obraz
LynseyC,

Zmodyfikowałem miarę „Zgrupowana korekta RDB”, tak aby przechodziła ona przez długi okres naliczania urlopu w wielu cyklach „praca / brak pracy”.
Ryan B.

2
Cześć, dziękuję za cały wysiłek, bardzo wdzięczny. Niestety modyfikacja nie rozwiązała problemu. Jeśli jednak usunęłem ostatni warunek w filtrze „Zostaw [ostatnia data przed ukończeniem pracy] <> Puste ()”, to rozwiązałem problem, ale ponownie
złamałem cielęta

Strzelać. Mam nadzieję, że znajdziesz coś, co działa.
Ryan B.

2

Trochę to zajęło, ale udało mi się wymyślić obejście. Zakładając, że wartość bilansowa dla pustych miejsc wynosi zawsze -1, a wartość wynosi 1 dla „Pracujących”, a dane są dostępne dla wszystkich dat bez odstępów, coś takiego jak poniższe obliczenie może działać:

Running Total = 
    VAR Employee = Leave[Employee ID]
    VAR Date1 = Leave[Date]
    VAR Prev_Blank = CALCULATE(MAX(Leave[Date]),
                        FILTER(Leave,Leave[Date] < Date1),
                        FILTER(Leave,Leave[Employee ID]=Employee),
                        FILTER(Leave,Leave[Type]=BLANK()))  
    VAR Day_count_Working = CALCULATE(COUNT(Leave[Date]),
                        FILTER(Leave,Leave[Date] > Prev_Blank),
                        FILTER(Leave,Leave[Date] <= Date1),
                        FILTER(Leave,Leave[Employee ID]=Employee),
                        FILTER(Leave,Leave[Type]="Working")) 
    VAR Day_count = CALCULATE(COUNT(Leave[Date]),
                        FILTER(Leave,Leave[Date] >= Prev_Blank),
                        FILTER(Leave,Leave[Date] <= Date1),
                        FILTER(Leave,Leave[Employee ID]=Employee)) 
RETURN (IF(Day_count_Working=BLANK(),Day_count,Day_count-1)-Day_count_Working)*-1 + Day_count_Working

Pamiętaj, że to może nie być gotowy produkt, ponieważ pracowałem z małą próbką, ale to powinno zacząć. Mam nadzieję że to pomoże.


Dzięki @ CR7SMS. Ponownie uruchamia sumę bieżącą, gdy typ = Działająca, ale suma bieżąca, gdy typ jest pusty, nie działa. Przez 7 listopada zmniejsza się do 3, ale następnie z 8-14 listopada zwraca -2. Czy możesz pomóc w zmianie kodu, aby suma bieżąca działała, gdy typ jest pusty? Dzięki
LynseyC

Cześć Lynsey. Próbowałem innego obliczenia. Dodałem ją jako kolejną odpowiedź, ponieważ obliczenia były nieco długie. Ale mam nadzieję, że nowe obliczenia działają.
CR7SMS

@ CR7SMS, unikaj dodawania więcej niż jednej odpowiedzi do jednego pytania. Wprowadza w błąd innych użytkowników, którzy mogą szukać podobnego problemu / rozwiązania i nie jest to miłe. Zamiast tego powinieneś dodać wszystko, co wymyślisz, jako rozwiązanie jednej odpowiedzi i podzielić każdy inny aspekt na sekcje.
Christos Lytras

2

Obliczenia są nieco długie, ale wydaje się, że działają w przykładowych danych, których używam. Wypróbuj to:

Running Total = 
    VAR Employee = Leave[Employee ID]
    VAR Date1 = Leave[Date]
    VAR Prev_Blank = CALCULATE(MAX(Leave[Date]),
                        FILTER(Leave,Leave[Date] < Date1),
                        FILTER(Leave,Leave[Employee ID]=Employee),
                        FILTER(Leave,Leave[Type]=BLANK()))  
    VAR Prev_Working = CALCULATE(MAX(Leave[Date]),
                        FILTER(Leave,Leave[Date] < Date1),
                        FILTER(Leave,Leave[Employee ID]=Employee),
                        FILTER(Leave,Leave[Type]="Working"))    
    VAR Prev_Blank1 = CALCULATE(MAX(Leave[Date]),
                        FILTER(Leave,Leave[Date] < Prev_Working),
                        FILTER(Leave,Leave[Employee ID]=Employee),
                        FILTER(Leave,Leave[Type]=BLANK()))  
    VAR Prev_type = CALCULATE(MAX(Leave[Type]),
                        FILTER(Leave,Leave[Date] = Date1-1),
                        FILTER(Leave,Leave[Employee ID]=Employee))
    VAR Prev_Blank2 = IF(Leave[Type]="Working" && (Prev_Blank1=BLANK() || Prev_type=BLANK()),Date1-1,Prev_Blank1)    
    VAR Day_count_Working = CALCULATE(COUNT(Leave[Date]),
                        FILTER(Leave,Leave[Date] > Prev_Blank2),
                        FILTER(Leave,Leave[Date] <= Date1),
                        FILTER(Leave,Leave[Employee ID]=Employee),
                        FILTER(Leave,Leave[Type]="Working")) 
    VAR Day_count = CALCULATE(COUNT(Leave[Date]),
                        FILTER(Leave,Leave[Date] >= Prev_Blank2),
                        FILTER(Leave,Leave[Date] <= Date1),
                        FILTER(Leave,Leave[Employee ID]=Employee)) 
RETURN (IF(Day_count_Working=BLANK(),Day_count,Day_count-1)-Day_count_Working)*-1 + Day_count_Working

Użyłem tutaj wielu zmiennych. Być może uda ci się wymyślić krótszą wersję. Zasadniczo chodzi o to, aby znaleźć poprzednie pierwsze wystąpienie „Praca”, aby znaleźć od czego zacząć obliczenia. Jest to obliczane w zmiennej „Prev_Blank2”. Kiedy znamy punkt początkowy (zaczyna się tutaj od 1), możemy po prostu policzyć liczbę dni za pomocą „Working” lub blank () pomiędzy Prev_Blank2 a datą bieżącego rekordu. Korzystając z tych dni, możemy zwrócić końcową wartość dla sumy całkowitej.

Mam nadzieję, że to załatwi sprawę;)

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.