cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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.

 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
1 ACCEPTED SOLUTION
Community Champion

@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 =
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 =
_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``````

11 REPLIES 11
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

Post Prodigy

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!

Community Champion

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

Am I missing something?

Post Prodigy

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!

Community Champion

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

Post Prodigy

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.

Community Champion

@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 =
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 =
_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``````

Post Prodigy

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

Community Champion

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

Community Champion

@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

Resolver III

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,

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors