Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I'm in need for a measure that shows me the cumulative total in a table. The table consist of a category column ("Reason") and a amount column ("Amount"). I am creating a pareto-chart and therefore I need to create a measure that shows me the cumulative total.
An illustration of what I want. I have the Reason and Amount columns, and want to create the column marked in red:
I have tried this measure, but I don't get the correct numbers from it:
note: "Total_amount" = SUM(events(amount))
Cumulative_total =
VAR Cumulative_test = [Total_amount]
RETURN
SUMX(
FILTER(
summarize(ALLSELECTED(events);events[Reason];
"Test";[Total_amount]);
events[amount] >= Cumulative_test);
events[amount]
)
Could anybody help me on this?
Solved! Go to Solution.
Hi,
Thank you for your replies, @amitchandak and @v-jingzhang.
I found a solution to this using this formula (a lot of similarities with the formulas you recommended):
reason Running Total =
VAR ReasonRank =
RANKX(
ALL(events[Reason]);
[Total_amount];;
DESC
)
VAR RunningTotal =
CALCULATE(
[Total_amount];
FILTER(
ALL(events[Reason]);
ReasonRank >= RANKX(
ALL(events[Reason]);
[Total_amount];;
DESC;
Dense)
)
)
RETURN
RunningTotal
Hi,
Thank you for your replies, @amitchandak and @v-jingzhang.
I found a solution to this using this formula (a lot of similarities with the formulas you recommended):
reason Running Total =
VAR ReasonRank =
RANKX(
ALL(events[Reason]);
[Total_amount];;
DESC
)
VAR RunningTotal =
CALCULATE(
[Total_amount];
FILTER(
ALL(events[Reason]);
ReasonRank >= RANKX(
ALL(events[Reason]);
[Total_amount];;
DESC;
Dense)
)
)
RETURN
RunningTotal
Hi @Henrik_PBI
Since your data is sorted by total amount descendingly, you can add a rank column to sort it and use this column in the cumulative total measure. Here is the PBIX file.
Create a summarized table:
Table2 = SUMMARIZE(Table1,Table1[relatertÅrsak],"total",SUM(Table1[amount]))
Add a rank column
rankIndex = RANKX(Table2,Table2[total])
Then create a measure
Cumulative total = SUMX(FILTER(ALL(Table2),Table2[rankIndex]<=MAX(Table2[rankIndex])),Table2[total])
Kindly let me know if this helps.
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.
@Henrik_PBI , Try like
Cumulative_total =
SUMX(
ADDCOLUMNS(
summarize(events;events[Reason];
"Test";[Total_amount]);
"Cumm", calculate(sum([Test]), filter(events, [Test] <= max( [Test]))));
[Cumm]
)
or
Cumulative_total =
SUMX(
FILTER(
summarize(allselected(events);events[Reason];
"Test";[Total_amount]);
[Test] <= max([Test]));
[Test]
)
Hi, @amitchandak - thank you for your reply.
I've tested your suggestion but I don't seem to get it right.
My formula:
Cumulative_total =
SUMX(
FILTER(
summarize(ALLSELECTED(events);events[Reason];
"Test";[Total_amount]);
[Test] <= [Test]);
[Test]
)
My output:
(the real reason names are hidden)
Any ideas on what I am doing wrong?
@Henrik_PBI , Try like
Cumulative_total =
SUMX(
ADDCOLUMNS(
summarize(events;events[Reason];
"Test";[Total_amount]);
"Cumm", calculate(sum([Test]), filter(events, [Test] <= max( [Test]))));
[Cumm]
)
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Hi, @amitchandak
I get an error when trying to write your formula:
How do I share an pbix file here?
@Henrik_PBI , put it like [Test]
You can upload to dropbox or onedrive and share link
@amitchandak, Thank you, I really appreciate your help.
I still get an error when writing your formula:
Link to sample pbix file:
https://www.dropbox.com/s/y6ti27rk50i3h4w/SampleData_PBI.pbix?dl=0
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |