Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 |
Solved! Go to 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
Step 1 - Create this calculated column:
Step 2 - Create this measure:
Step 3 - On the visual sort the X axis ascending
In case it solved your question. please mark this as a solution
@PBI_newuser
This is what I get what my method and the sample data you shared:
Am I missing something?
@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.
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
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!
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 :
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
59 | |
57 |