Forum Discussion
Cumulative %
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.
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
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.