Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
New to Power BI and trying to learn how to use cumulative in Power BI but without any success. Have read a dozen different topics here in the community but without luck.
My data are as below in table 1 and then the graph illustrates what I want it to look like in Power Bi. The data I have are in one of my matrixes in BI where the table "% grand total volume" are a calculation from the Volume column. Furthermore this data is a filtered data where I actually have more names in the list but these should not show here (total volume of 400 something).
Tried creating two measures like this but didnt work.
% of volume=
DIVIDE (
SUM ( Table1[Volume] );
CALCULATE (
SUM ( Table1[Volume] );
ALL ( Table1[Name] ) ))
Cumulative % =
VAR Name =
MAX ( Table1[Name] )
RETURN
CALCULATE ( [% of grand total volume]; Table1[Name] <= Name )
Thank you for helping me!
Solved! Go to Solution.
Hi @_Xandyr_
Is this what you want?
If so, create a calculated column
rank column = RANKX(FILTER(Sheet1,Sheet1[category]=EARLIER(Sheet1[category])),[volume],,DESC,Dense)
then create measures
sum = SUM(Sheet1[volume])
% = SUM(Sheet1[volume])/CALCULATE(SUM(Sheet1[volume]),ALLSELECTED(Sheet1))
clc% =
CALCULATE (
SUM ( Sheet1[volume] ),
FILTER (
ALLSELECTED ( Sheet1 ),
Sheet1[category] = MAX ( Sheet1[category] )
&& Sheet1[rank column] <= MAX ( Sheet1[rank column] )
)
)
/ CALCULATE ( SUM ( Sheet1[volume] ), ALLSELECTED ( Sheet1 ) )
Hi @_Xandyr_
Is this what you want?
If so, create a calculated column
rank column = RANKX(FILTER(Sheet1,Sheet1[category]=EARLIER(Sheet1[category])),[volume],,DESC,Dense)
then create measures
sum = SUM(Sheet1[volume])
% = SUM(Sheet1[volume])/CALCULATE(SUM(Sheet1[volume]),ALLSELECTED(Sheet1))
clc% =
CALCULATE (
SUM ( Sheet1[volume] ),
FILTER (
ALLSELECTED ( Sheet1 ),
Sheet1[category] = MAX ( Sheet1[category] )
&& Sheet1[rank column] <= MAX ( Sheet1[rank column] )
)
)
/ CALCULATE ( SUM ( Sheet1[volume] ), ALLSELECTED ( Sheet1 ) )
Hello Maggie,
Yes this looks like what i want! I'm not very familiar with your solution but will look at it and try to make it work with my original/real data.
Thanks!
Hi @_Xandyr_
When i add columns in a line and clustered column chart, there is no "total" column in this chart.
We could create a static calculated table to add "total" column in the chart, but it is static and not changed by the slicer/filters.
https://community.powerbi.com/t5/Desktop/Grand-Total-in-Bar-Chart/td-p/612631
Also, would you like the chart only to show top 11 "volume" name?
If not , what do you mean by this sentence?
"Furthermore this data is a filtered data where I actually have more names in the list but these should not show here (total volume of 400 something)"
Hello @v-juanli-msft ,
Thank for replying!
Im not after creating a static calculated table, rather I would like something like the two measures I created (but can't figure out on how to get them to work).
Well that depends, I want it to be fully dynamic. Maybe I wasn't fully clear but I have a filter right now with two different categories; Company and Non-profit. So in the chart I showed I only want the volume from Company showing and then I want to create a cumulative line from "% grand total of volume".
Made this in Power BI, maybe this helps a little.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |