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.

lbendlin's avatar
lbendlin
Super User
4 years ago

Please provide usable sanitized sample data that fully covers your issue.

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