Forum Discussion
Need help with DAX Measure to for Cumulative %.
I am trying to replicate the column in yellow in Power BI.
In Power BI I already have the rank working along with the totals and complete total (257 in first screen shot)
In Excel the cumulative refers to the % of total. I was able to replicate that.
Here is the tricky part. It takes the 35% for Rank 1, then for
Rank 2 the 35% in K then + the 12% in J (=K6+J7)
Rank 3 = 60% in Column K +12% in J (=K7+J8)
I cannot figure out how to calculate this. We filter this by week and week end dates.
I tried this and it did not even come close.
Cumulative Rate1 =
VAR _NCMDate3 = MAX('NCMDefects_Last60Days'[Week])
VAR _NCMDefectQty = [Total Defect Qtys]
VAR _NCMDefect2 = MAX('NCMDefects_Last60Days'[Defect])
VAR _NCMrate = [Total Defect Qtys]/[Total NCM Qty] -- This is the % of Total --
VAR _NCMTable4 =
SUMMARIZE(
FILTER(ALL('NCMDefects_Last60Days'),[Week] = _NCMDate3),
[Defect],
"Rate",
[Total Defect Qtys]/[Total NCM Qty]
)
VAR _NCMTable5 = ADDCOLUMNS(_NCMTable4,"Cumulative Rate",SUMX(_NCMTable4,[Rate]))
RETURN
MAXX(FILTER(_NCMTable5,[Defect]=_NCMDefect2),[Rate])
Any help you can provide will be greatly appreciated.
I got it working: I created 2 measures:
Total Defects Part No Cumulative % =VAR CurrentRank = [DefectPartRankv2]RETURNSUMX(FILTER(ALLSELECTED('NCMDefects_Last60Days'),[DefectPartRankv2] <= CurrentRank),[Total Defects Part No])Then this one:Total Defects Part No Cumulative % All = [Total Defects Part No Cumulative %]/[Total Defects Part No All]That seemed to resolve it, I took a step back and looked at the qtys and then total qtys.Thank you for your help anyway!
7 Replies
IrwanSuper User
hello Anonymous
please check if this accomodate your need.
looking at your DAX, i assumed you want to calculate this in calculated column (not measure).
create a calculated column wiht following DAX
Cummulative =
var _TotalSum = CALCULATE(SUM('Table'[Qty]),ALL('Table'))
var _CumulativeSum = CALCULATE(SUM('Table'[Qty]),FILTER(ALL('Table'),'Table'[Defect Rank]<=EARLIER('Table'[Defect Rank])))
Return
DIVIDE(_CumulativeSum,_TotalSum)and for filtering this result in time manner, it would be great if you can provide sample data with time value since there is no time column in your sample data above.Hope this will help you.Thank you.When I got to the last part, it did not like it. Irwan
DefectPartRank = VAR _NCMDate = MAX('NCMDefects_Last60Days'[Week]) VAR _NCMPartNum = MAX('NCMDefects_Last60Days'[PartNum]) VAR _NCMTable = SUMMARIZE( FILTER(ALL('NCMDefects_Last60Days'),'NCMDefects_Last60Days'[Week] = _NCMDate), [PartNum], "Qty", SUM('NCMDefects_Last60Days'[DefectQty]) ) VAR _Table1 = ADDCOLUMNS(_NCMTable,"Rank",RANKX(_NCMTable,[Qty])) RETURN MAXX(FILTER(_Table1,[PartNum]=_NCMPartNum),[Rank])The Dax Code for Defect Part Rank works and I filter out to "IS 1" for the week I am working on and that works to get the max Qty for the part #. The max by part number can change by week and that seems to work. It is the cumulative that is not. Based on the Total Defects Part No% in my screenshot below. (Just a simple % of Total Calculation):
Total Defects Part No = CALCULATE('NCMDefects_Last60Days'[Total Defect Qtys]Total Defects Part No % = [Total Defects Part No]/[Total Defects Part No All]Total Defects Part No All = CALCULATE(('NCMDefects_Last60Days'[Total Defects Part No]),ALL('NCMDefects_Last60Days'[Defect]))
Irwan How do I attach a sample file or PBIX? I do not see where I can do that? I only see pictures, I tried pasting and it would not allow me to exceed 2000 characters
- Irwan
hello Anonymous
for dropping your pbix, please use dropbox or something similar.
also for your previous error, did you make that DAX in calculated column?
seems the error occurs because you are using measure for Cumulative calculation.
as far as i know, EARLIER function does not work with direct column in measure.
as your previous query,
the cumulative doesnt work because will always calculate the total no matter time filter you apply because you are using ALL in your DAX. ALL function will always calculate all the data you have in that respective table.
Hope this will help.
Thank you.
Irwan what is a good email to send you the drop box link? or try this:
Not sure I understand what you are saying above. I need to use the ALL to get the % of total. The cumulative calcs refer to the % of total unless the stakeholder was doing that incorrectly, I am just trying to replicate what was done in Excel.
I got it working: I created 2 measures:
Total Defects Part No Cumulative % =VAR CurrentRank = [DefectPartRankv2]RETURNSUMX(FILTER(ALLSELECTED('NCMDefects_Last60Days'),[DefectPartRankv2] <= CurrentRank),[Total Defects Part No])Then this one:Total Defects Part No Cumulative % All = [Total Defects Part No Cumulative %]/[Total Defects Part No All]That seemed to resolve it, I took a step back and looked at the qtys and then total qtys.Thank you for your help anyway!