Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    I got it working:  I created 2 measures:

    Total Defects Part No Cumulative % =
    VAR CurrentRank = [DefectPartRankv2]
    RETURN
    SUMX(
        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!
Irwan's avatar
Irwan
Icon for Super User rankSuper User
2 years ago

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.
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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]))