Forum Discussion
Helper INeed help creating Running Total & Rank
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
Helper IGreg_Deckler Hi, thank you for helping out. I tried to use your formula to my original main file and it doesnt work. I realised i have forgotten to include a column in the sample file raw data
Based on the new raw file v1, the Rank Measure formula and Running Total Measure are not working as intended... can you please advise? Thank you.
Uploaded the pbi file here
https://drive.google.com/file/d/1Gc8cdGVOcow0mYwUojmQQkVeVeQ1PCXO/view?usp=sharing
Community ChampionMunch00 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
Greg_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
Munch00 Just use ALLSELECTED instead of ALL
- Munch00
Greg_Deckler Indeed that works, thanks a ton!