Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
_Xandyr_
Helper II
Helper II

Creating a cumulative line from % of grand total from a matrix

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). 

 

Cumulative.JPG

 

 

 

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!

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @_Xandyr_ 

Is this what you want?

Capture1.JPG

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 ) )
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-juanli-msft
Community Support
Community Support

Hi @_Xandyr_ 

Is this what you want?

Capture1.JPG

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 ) )
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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!

v-juanli-msft
Community Support
Community Support

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

https://community.powerbi.com/t5/Desktop/Possible-Analytics-function-dynamic-Total-column-in-chart-w...

 

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)"

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

 

 

 

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. Power BI - cumulative.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.