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
PBI_newuser
Post Prodigy
Post Prodigy

Cumulative total by group and sum

Hi, how to group the Products with Status = EOL first and then products with Status = Current, then arrange the EOL from low to high and the rest also from low to high and do it cumulative. Below screenshot is the expected output.

PBI_newuser_0-1651041813967.png

 

 

ProductStatusRevenue
A1EOL $         4,121
A2EOL $         6,409
A3EOL $       12,971
A4EOL $       23,522
A5EOL $       69,586
A6EOL $       96,668
A7EOL $    103,490
A8EOL $    317,191
A9EOL $    470,015
A10EOL $    805,573
A11EOL $ 1,166,869
A12EOL $ 3,985,437
A13Current $       29,626
A14Current $       34,263
A15Current $    120,558
A16Current $    206,031
A17Current $    315,247
A18Current $    459,955
A19Current $    546,466
A20Current $ 1,077,219
A21Current $ 1,266,182
A22Current $ 1,324,749
A23Current $ 1,339,427
A24Current $ 1,401,335
A25Current $ 2,087,884
A26Current $ 2,137,316
A27Current $ 2,233,231
A28Current $ 2,777,508
A29Current $ 3,294,716
A30Current $ 6,498,735
A31Current $ 8,498,814
1 ACCEPTED SOLUTION

@PBI_newuser 
This is the measure (without using static columns):

Cumulative Total = 
VAR _current_platform = SELECTEDVALUE('Append Table'[Platform])
VAR _current_status = SELECTEDVALUE('Append Table'[Status])
VAR _revenue = CALCULATE(SUM('Append Table'[Net Price]))
VAR _factor = 
        SWITCH( 
            TRUE(),
            _current_status = "EOL", 1,
            _current_status = "Current", 2
        )
VAR _table =
    ADDCOLUMNS(
        CALCULATETABLE(
            SUMMARIZE(
                'Append Table',
                'Append Table'[Platform],
                'Append Table'[Status]
            ),
            ALLSELECTED()
        ),
        "@Factor", SWITCH( 
                    TRUE(),
                    [Status] = "EOL", 1,
                    [Status] = "Current", 2
                ),
        "@Total_Revenue_Per_Platform", CALCULATE(SUM('Append Table'[Net Price]))
    )
VAR _max =
    MAXX(
        _table,
        [@Total_Revenue_Per_Platform]
    )
VAR _rank_table = 
    ADDCOLUMNS(
        _table,
        "@rank", _max * [@Factor] + [@Total_Revenue_Per_Platform]
    )
VAR _current_rank = _max * _factor  + _revenue
VAR _result = 
    SUMX(FILTER(_rank_table, [@rank] <= _current_rank), [@Total_Revenue_Per_Platform])
RETURN
    _result

 

In case it solved your question. please mark this as a solution. Appreciate your Kudos!

View solution in original post

11 REPLIES 11
SpartaBI
Community Champion
Community Champion

@PBI_newuser 
Step 1 - Create this calculated column:

Rank =
VAR _max = MAX('Table'[Revenue])
VAR _factor = IF('Table'[Status] = "EOL", 1, 2)
VAR _result = _max * _factor + 'Table'[Revenue]
RETURN
_result

Step 2 - Create this measure:

Cumulotive Total =
VAR _current_rank = MAX('Table'[Rank])
VAR _result =
CALCULATE(
SUM('Table'[Revenue]),
'Table'[Rank] <= _current_rank,
REMOVEFILTERS('Table')
)
RETURN
_result



Step 3 - On the visual sort the X axis ascending

In case it solved your question. please mark this as a solution

Hi @SpartaBI , thank you your help. I tried to use your suggestion Column and Measure but it doesn't work. The Revenue is not sorting in ascending order and the cumulative total is incorrect. Could you please help on this? Here is the pbix file. Thank you! 

PBI_newuser_0-1651133191322.png

 

@PBI_newuser 
This is what I get what my method and the sample data you shared:

SpartaBI_0-1651135615538.png

Am I missing something?

Hi @SpartaBI , your solution works well for the data I posted, but actually my data is much more complicated as the Revenue is actually a measure. Here is the pbix file. Could you please take a look at it and suggest which measure/column should I modify? Thank you so much!

@PBI_newuser , you are right, it is much more complicated 🙂 Next time post exactly the data in the model cause it changes the logic of the solution entirely.
This is the rank column:

 

 

Rank = 
VAR _table =
    ADDCOLUMNS(
        VALUES('Append Table'[Product]),
        "@Total_Revenue_Per_Product", CALCULATE(SUM('Append Table'[Net Price]), ALLEXCEPT('Append Table','Append Table'[Product]))
    )
VAR _max =
    MAXX(
        _table,
        [@Total_Revenue_Per_Product]
    )
VAR _factor = IF('Append Table'[Status] = "EOL", 1, 2)
VAR _result = _max * _factor + CALCULATE(SUM('Append Table'[Net Price]), ALLEXCEPT('Append Table','Append Table'[Product]))
RETURN
    _result

 

 


This is the measure:

 

 

Cumulative Total = 
VAR _current_rank = MAX('Append Table'[Rank])
VAR _result =
CALCULATE(
SUM('Append Table'[Net Price]),
'Append Table'[Rank] <= _current_rank,
REMOVEFILTERS('Append Table'[Platform], 'Append Table'[Status])
)
RETURN
_result

 

 


This is what I see now:

SpartaBI_0-1651137495520.png


In case it solved your question. please mark this as a solution. Appreciate your Kudos.

Hi @SpartaBI , okay, will take note when I post data next time, thank you! 🙂

Is it possible to sort the EOL from low to high and then Current also from low to high and do it cumulative? E.g. P8 is the lowest for EOL, it should be in the first row.

PBI_newuser_2-1651156014261.png

 

@PBI_newuser 
This is the measure (without using static columns):

Cumulative Total = 
VAR _current_platform = SELECTEDVALUE('Append Table'[Platform])
VAR _current_status = SELECTEDVALUE('Append Table'[Status])
VAR _revenue = CALCULATE(SUM('Append Table'[Net Price]))
VAR _factor = 
        SWITCH( 
            TRUE(),
            _current_status = "EOL", 1,
            _current_status = "Current", 2
        )
VAR _table =
    ADDCOLUMNS(
        CALCULATETABLE(
            SUMMARIZE(
                'Append Table',
                'Append Table'[Platform],
                'Append Table'[Status]
            ),
            ALLSELECTED()
        ),
        "@Factor", SWITCH( 
                    TRUE(),
                    [Status] = "EOL", 1,
                    [Status] = "Current", 2
                ),
        "@Total_Revenue_Per_Platform", CALCULATE(SUM('Append Table'[Net Price]))
    )
VAR _max =
    MAXX(
        _table,
        [@Total_Revenue_Per_Platform]
    )
VAR _rank_table = 
    ADDCOLUMNS(
        _table,
        "@rank", _max * [@Factor] + [@Total_Revenue_Per_Platform]
    )
VAR _current_rank = _max * _factor  + _revenue
VAR _result = 
    SUMX(FILTER(_rank_table, [@rank] <= _current_rank), [@Total_Revenue_Per_Platform])
RETURN
    _result

 

In case it solved your question. please mark this as a solution. Appreciate your Kudos!

Hi @SpartaBI , thank you soooo much for your help! It works now!! 😀

My pleasue 🙂
P.S. check out my showcase report, maybe you will find there some cool stuff you will like:
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543

@PBI_newuser hey, yes.

I actually missed that issue.

The rank column I did is not enough in this case because it is fixed for the whole years total in the table, and you have a dynamically changing filter context coming from the year slicer. This means I will need to add the logic of the ranking in a temporary table inside the measure itself and we will not use the column at all. I hope to get to it by tomorrow and reply with the final measure

Hey @PBI_newuser ,

I did the first same column than @SpartaBI but then a created a second column cumulativ total :

Cumulotive Total =
CALCULATE( SUM('Sheet1'[Revenue]),
FILTER(Sheet1,
Sheet1[Rank] <= EARLIER(Sheet1[Rank])
)
)
Which gives the desired output.

Best regards,

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.