Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Munch00
Helper I
Helper I

Need 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

Munch00_1-1684255054805.png

 

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. 

 

Munch00_2-1684255282205.png

Thank you.

1 ACCEPTED SOLUTION

@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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User
Super User

Hi,

You may remove the "Concatenate" column in the Query Editor.  Here is my solution.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Greg_Deckler
Super User
Super User

@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
    __Result

See PBIX attached below signature.

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

@Greg_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

 

Munch00_1-1684290771211.png

 

 

@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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

@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.

 

Munch00_0-1684416268001.png

 

@Munch00 Just use ALLSELECTED instead of ALL



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler Indeed that works, thanks a ton!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.