cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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.

 

cummulative %.JPG

 

 

Also please advise how to attached the .pbix file when raising a help request on this forum.

 

Thanks in advance

Mahad

1 ACCEPTED SOLUTION

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.

View solution in original post

10 REPLIES 10
Ashish_Mathur
Super User
Super User

Hi,

Share the link from where i can download your PBI file.  You may upload the file to Google Drive/One Drive or any such similar service and share the download link.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
lbendlin
Super User
Super User

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.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Anonymous
Not applicable

Dear Ibendlin

 

Thank you for your response. I'm expecting the result as below.

Appreciate your help on this.

 

MAHAD_0-1659286075849.png

 

Thanks

Mahad

 

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

Anonymous
Not applicable

 

Dear Ibendlin

 

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

Sales Table.JPGStore Master Table.JPG

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

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.

Anonymous
Not applicable

Dear Ibendlin

 

PFB the google drive link for the sample data.

 

https://drive.google.com/file/d/1fhIvmngE2KiN90G-5vBuJiuPY4iT-wCI/view?usp=drive_web 

 

Thanks

Mahad

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

veasonfmsft_0-1659410288276.png

Best Regards,
Community Support Team _ Eason

Anonymous
Not applicable

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

 

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.

Helpful resources

Announcements
Exciting changes

Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Kudo Data Story carousel

Data Stories Gallery

Visit our Data Stories Gallery and give kudos to your favorite Data Stories.

Top Solution Authors