Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
98 | |
75 | |
63 | |
62 |
User | Count |
---|---|
135 | |
105 | |
104 | |
80 | |
65 |