Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Cumulative %

Hi   Can someone please help me to achieve the "cumulative %" measure based on the current displayed data. If possible please attached the Dax Code with an example.       Also please adv...
Need Help
  • lbendlin's avatar
    lbendlin
    4 years ago
    Cumul % = 
var cr = sum('Sales Table'[Qty])
var s = summarize(allselected('Sales Table'),'Sales Table'[Store],"q",sum('Sales Table'[Qty]))
var t = filter(s,[q]>=cr)
return divide(sumx(t,[q]),sumx(s,[q]))

     

    Note that you have ties in your data so the result is a bit ugly.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

 

Dear Ibendlin

 

Below are the "sales table" and "store master table"

here the Qty Measure = sum('Sales Table'[Qty])
 
I have similar scenario in current report. Please help me to achieve the cumulative % based on the above table data. 
 
Thanks 
Mahad
lbendlin's avatar
lbendlin
Super User
4 years ago

Please provide sanitized sample data that fully covers your issue. I cannot help you without usable sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive. I cannot use screenshots of your source data.

    • v-easonf-msft's avatar
      v-easonf-msft
      Community Support
      4 years ago

      Hi, Anonymous 

      Please try formulas like below:

      calculated column:

      Qty = CALCULATE(SUM('Sales Table'[Qty]),FILTER('Sales Table','Sales Table'[Store]=StoreMaster_Table[Store]))
      Rank_store = RANKX(StoreMaster_Table,StoreMaster_Table[store],,ASC,Dense)
      Rank_qty = RANKX(StoreMaster_Table,StoreMaster_Table[Qty],,DESC,Dense)
      count_rows = CALCULATE(COUNTROWS(StoreMaster_Table),ALL(StoreMaster_Table))
      Rank = RANKX(StoreMaster_Table,StoreMaster_Table[Rank_qty]+StoreMaster_Table[Rank_store]/StoreMaster_Table[count_rows],,ASC,Dense)

      Measure:

      Qty% = 
VAR cumulative =
    CALCULATE (
        SUM ( StoreMaster_Table[Qty] ),
        FILTER (
            ALLSELECTED ( StoreMaster_Table ),
            StoreMaster_Table[Rank] <= MAX ( StoreMaster_Table[Rank] )
        )
    )
VAR total =
    CALCULATE ( SUM ( StoreMaster_Table[Qty] ), ALLSELECTED ( StoreMaster_Table ) )
RETURN
    cumulative / total

      Best Regards,
      Community Support Team _ Eason

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Dear Eason

         

        Thank you so much for your time and help.


        The DAX code works perfectly fine, but they are not dynamic.
        My real time reports have huge data and I have the area and region as well.

         

        1. This report has restricted area and region wise to display only data of the access granted.
        2. Also we will view the report for monthly, quartely, and annualy.

         

        The ranking should change dynamically according to selection.
        The currently DAX code we have maintained the data in the column and hence we are getting wrong value when we filter the data.

         

        Appreciate your help to get the above results.

         

        Thanks
        Mahad