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
Ashish_Mathur
Super User
3 years agoHi,
You may remove the "Concatenate" column in the Query Editor. Here is my solution.
Hope this helps.