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

[Anonymous]

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!

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

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

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.

[Anonymous]

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]

Hi @Anonymous 

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-with/m-p/366010#M165562

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.

[Anonymous]

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

[Anonymous]

 Made this in Power BI, maybe this helps a little.