Forum Discussion
Munch00
Helper I
3 years agoNeed help creating Running Total & Rank
Hi Everyone I uploaded a sample PBI file for reference for below question. https://drive.google.com/file/d/1MuOgFDDad047V8U_Epcse9XBigk84lyk/view?usp=sharing Appreciate if anyone can help m...
- 3 years ago
Munch00 Definitely makes a difference. Here are the adjusted formulas and the PBIX is attached below.
Rank Measure v1 = VAR __Segment = MAX('Raw file v1'[SEGMENT]) VAR __BU = MAX('Raw file v1'[BU]) VAR __Line = MAX('Raw file v1'[Line No.]) VAR __Table = SUMMARIZE( ALL('Raw file v1'), [SEGMENT], [BU], [Line No.], "__TotalAmt", SUM('Raw file v1'[Total Amt])) VAR __Table1 = ADDCOLUMNS( __Table, "__Rank", RANKX( __Table, [__TotalAmt])) VAR __Result = MAXX(FILTER(__Table1, [SEGMENT] = __Segment && [BU] = __BU && [Line No.] = __Line), [__Rank]) RETURN __Result Running Total Measure v1 = VAR __Segment = MAX('Raw file v1'[SEGMENT]) VAR __BU = MAX('Raw file v1'[BU]) VAR __Line = MAX('Raw file v1'[Line No.]) VAR __Table = SUMMARIZE( ALL('Raw file v1'), [SEGMENT], [BU], [Line No.], "__TotalAmt", SUM('Raw file v1'[Total Amt])) VAR __Table1 = ADDCOLUMNS( __Table, "__RT", SUMX(FILTER(__Table, [__TotalAmt] >= EARLIER([__TotalAmt])),[__TotalAmt])) VAR __Result = MAXX(FILTER(__Table1, [SEGMENT] = __Segment && [BU] = __BU && [Line No.] = __Line), [__RT]) RETURN __Result
Munch00
Helper I
3 years agoGreg_Deckler I also need the Rank measure and Running Total measure to dynamically calculate depending on the filter slicer selection as well.... ie (Rank should be 1,2,3 and running total $223 858, $227 858, $229 858). Is there a way to do so? Thank you.
Greg_Deckler
Community Champion
3 years agoMunch00 Just use ALLSELECTED instead of ALL
- Munch003 years ago
Helper I
Greg_Deckler Indeed that works, thanks a ton!