Cumulative total by group and sum

[PBI_newuser]

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.

Product	Status	Revenue
A1	EOL	$         4,121
A2	EOL	$         6,409
A3	EOL	$       12,971
A4	EOL	$       23,522
A5	EOL	$       69,586
A6	EOL	$       96,668
A7	EOL	$    103,490
A8	EOL	$    317,191
A9	EOL	$    470,015
A10	EOL	$    805,573
A11	EOL	$ 1,166,869
A12	EOL	$ 3,985,437
A13	Current	$       29,626
A14	Current	$       34,263
A15	Current	$    120,558
A16	Current	$    206,031
A17	Current	$    315,247
A18	Current	$    459,955
A19	Current	$    546,466
A20	Current	$ 1,077,219
A21	Current	$ 1,266,182
A22	Current	$ 1,324,749
A23	Current	$ 1,339,427
A24	Current	$ 1,401,335
A25	Current	$ 2,087,884
A26	Current	$ 2,137,316
A27	Current	$ 2,233,231
A28	Current	$ 2,777,508
A29	Current	$ 3,294,716
A30	Current	$ 6,498,735
A31	Current	$ 8,498,814

[SpartaBI]

@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

[PBI_newuser]

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! 

[SpartaBI]

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

Am I missing something?

[PBI_newuser]

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!

[SpartaBI]

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

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

[PBI_newuser]

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.

[SpartaBI · 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!

[PBI_newuser]

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

[SpartaBI]

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

[SpartaBI]

@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

[philouduv]

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,