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

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)

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    harirao , Try like

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

    • harirao's avatar
      harirao
      Post Prodigy
      5 years ago

      Hello amitchandak,

       

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





      Regards,

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Community Support
    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.

    • 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,

      • Anonymous's avatar
        Anonymous
        Not applicable
        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)