Forum Discussion

harirao's avatar
harirao
Post Prodigy
5 years ago
Solved

Cumulative %

Hello Team,   Required your assistance by seeing in BI community I was able to calculate using Cumulative %, but not able to match with Excel calculation. Please find the below result getting in...
Need Help
Tips and Tricks
Tutorial Requests
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    5 years ago

    Hi harirao ,

     

    We create a sample based on your screenshot and we can use the measure to meet your requirement.

    We need to create an index column in Power Query Editor.

     

     

    Then create a measure like this,

     

    Cumulative % = 
var _total = CALCULATE(SUM('Table'[Diff]),ALLSELECTED('Table'))
var _cumulative = CALCULATE(SUM('Table'[Diff]),FILTER(ALLSELECTED('Table'),'Table'[Index]<=MAX('Table'[Index])))
return
DIVIDE(_cumulative,_total)

     

     

    If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

     

    Best regards,

     

    Community Support Team _ zhenbw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    BTW, pbix as attached.

  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi harirao ,

     

    You can use the RANKX function to create the Index Value.

     

    Create a Calculated Column

     

    Index = RANKX('Table','Table'[Diff])
     
    Then follow the same steps provided by v-zhenbw-msft  above.
     
    Regards,
    Harsh Nathani
    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
harirao's avatar
harirao
Post Prodigy
5 years ago

Hello v-zhenbw-msft 

 

Thanks for your reply, please note I have created new table by using DAX expression, so i am unable see this table in Transform Data to include "Index Column".
Can you please suggest any other alternate to work on this cumulative%?

Regards,

v-zhenbw-msft's avatar
v-zhenbw-msft
Community Support
5 years ago

Hi harirao ,

 

How about the result after you follow the suggestions mentioned in Anonymous and my original post?

Could you please provide more details or expected result about it If it doesn't meet your requirement?

If you've fixed the issue on your own please kindly share your solution. If the above posts help, please kindly mark it as a solution to help others find it more quickly.

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.