Forum Discussion
Helper INeed 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 me out on below. I need to create a running total column like this below...read up severel forums but still could not get it right
Also, i managed to create the RANK measue in my pbi file but i did it so with a Concatenate column. Is there any other ways to formulate it? Ideally i do not want the concatenate column in my visual but if i removed it, the RANK measure does not work as intended.
Thank you.
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
7 Replies
- Greg_Deckler
Community Champion
Munch00 Not sure why you need the Concatenate column, your Rank measure should just be this:
Rank Measure = RANKX( ALL('Raw file'), CALCULATE( SUM([Total Amt]) ) )As for your running total, you would generally need something to define "before". However, you could also do this:
Running Total Measure = VAR __TotalAmt = MAX('Raw file'[Total Amt]) VAR __Table = FILTER(ALL('Raw file'), [Total Amt] >= __TotalAmt) VAR __Result = SUMX( __Table, [Total Amt]) RETURN __ResultSee PBIX attached below signature.
Munch00Greg_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
- Greg_Deckler
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
- Ashish_Mathur
Super User
Hi,
You may remove the "Concatenate" column in the Query Editor. Here is my solution.
Hope this helps.
