Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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

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 perce...
General Comment
Need Help
Show and Tell
Tips and Tricks
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    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

v-frfei-msft's avatar
v-frfei-msft
Icon for Community Support rankCommunity Support
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      v-frfei-msft
      Icon for Community Support rankCommunity Support
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        v-frfei-msft  Thanks!