Cumulative %

[harirao]

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 Excel

Measure used for  calculating Cumulative %
1) ABS Difference =

CALCULATE(SUM('Location_Only_Overall'[ABS]),

    FILTER(ALLSELECTED('Location_Only_Overall'[Theater]),

        ISONORAFTER('Location_Only_Overall'[Theater], MIN('Location_Only_Overall'[Theater]), ASC)))

2) ABS% =

var Total = [ABS Difference]

var statusall = CALCULATE([ABS Difference],ALLSELECTED(Location_Only_Overall))

Return

Divide(sumx(FILTER(SUMMARIZE(ALLSELECTED(Location_Only_Overall),Location_Only_Overall[Raw Drive],"Total$" ,[ABS ifference]),

[Total$]>=Total),[Total$]),statusall,0)

In power bi i am not getting correct result for Eg: row 3 &4 i am getting 32% &37%, but in excel its 24% & 29% so on.

Thank you

Regards,

[v-zhenbw-msft · Accepted solution]

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.

[v-zhenbw-msft · Accepted solution]

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.

[harirao]

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]

Hi @harirao ,

How about the result after you follow the suggestions mentioned in @harshnathani 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.

[harshnathani · Accepted solution]

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)

[amitchandak]

@harirao , Try like

ABS% =
Divide( [ABS Difference], CALCULATE(SUM('Location_Only_Overall'[ABS]),ALLSELECTED(Location_Only_Overall)))

[harirao]

Hello @amitchandak,

Thanks for your response, after using above dax measure not getting correct result, please find the screen shot for your reference.

Regards,