Power BI Desktop | How can i do a Cumulative Percent Calculation

[Anonymous]

Hi everyone,

iam struggeling to make a cumulative percent calculation depent "Value" ( "Betrag" in German) for supplier.

I have made a percent calculation and it works well, but cumulative percent doesnt work.

Information about my Dataset:

I have many Supplier with value for each month and year.

For example one supplier:

So in my Dashboard i have a supplier table and looks like: (kumProzent -> cumulative Percent calculation) (Code is below)

So the result should be look like this: ( look last Picture, but it have a other Dataset(not mine) & the cumulative percent Value is right)

I thank you in advance and hope you can help me.

Kind regards,

Unknown

[v-frfei-msft]

Hi @Anonymous ,

One sample for your reference. If it doesn't meet your requirement, kindly share your sample data and excepted result to me. Please upload your files to One Drive and share the link here.

Here I create two measures.

Prozent = var sum1 = CALCULATE(SUM(Table1[betrag]),ALLSELECTED(Table1))
var sum2 = CALCULATE(SUM(Table1[betrag]))
return
DIVIDE(sum2,sum1)

Measure = 
CALCULATE (
    SUMX (
        FILTER ( ALL ( Table1[index] ), Table1[index] >= MAX ( Table1[index] ) ),
        [Prozent]
    )
)

Regards,

Frank

[Anonymous]

Hi @v-frfei-msft ,

thanks for you response and i have try your solution.

But unfortunatly it doesnt work, i think because i have multiple rows for suppliers. (look power bi file)

File (table snipped & data modified):

https://1drv.ms/u/s!Ar9P7_DTkCuW5Rd0x9MEaRS7Zj2v

And a additional question how to make a Rank with this power bi sample?
Because if i have only one row for each supplier with year, month, value thats easy. (your example)

But at the other hand with multiple same suppliers thats not easy and i get the classic error: "A single value for column 'Lieferant'(supplier) in table 'Table' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

Thanks thanks again for helping !!!

Kind regards,
Unknown

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

To create a calculated column as below.

Table2 = SUMMARIZECOLUMNS('Table'[Supplier],'Table',"value1",SUM('Table'[Value]),"perc",[percent],"id",MAX('Table'[SupplierID]))

Then add a calculated column in Table2.

rank = RANKX(ALL(Table2),Table2[perc],,DESC,Dense)

After that we can create a measure to get the excepted result as we need.

Measure 2 = CALCULATE(SUM(Table2[perc]),FILTER(ALLSELECTED(Table2),Table2[rank]<=MAX(Table2[rank])))

Regards,

Frank

[Anonymous]

@v-frfei-msft  Thanks!