Forum Discussion

sparkymark75's avatar
sparkymark75
Frequent Visitor
7 years ago
Solved

Using DAX to get a cumulative total using measures

I have the following measures defined in a dataset;

 

DOAs = CALCULATE(COUNT('Incident'[TicketNumber]),'Incident'[new_installoutcome] = "100000000")
FOAs = CALCULATE(COUNT('Incident'[TicketNumber]),'Incident'[new_installoutcome] = "100000001")
InstallAttempts = CALCULATE(COUNT('Incident'[TicketNumber]),'Incident'[new_installoutcome] = "100000000",'Incident'[new_installoutcome] = "100000001",'Incident'[new_installoutcome] = "100000002")
InstallFailRate = DIVIDE((DOAs + FOAs), InstallAttempts)

What I now need is a measure which is a sum of the DOA and FOA measures divided by a sum of the install attempts. If this worked it would look like this...

 

InstallFailRate = DIVIDE((SUM(DOAs) + SUM(FOAs)), SUM(InstallAttempts))

How do I achieve this in DAX? Do I have to break use the root calculation of each measure in the new measure?

Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi sparkymark75 

    You may try below measure to get the cumulative total of a measure.For example:

    Measure =
CALCULATE (
    [DOAs],
    FILTER ( ALL ( Incident ), Incident[date] <= MAX ( Incident[date] ) )
)

    Regards,

1 Reply

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    Hi sparkymark75 

    You may try below measure to get the cumulative total of a measure.For example:

    Measure =
CALCULATE (
    [DOAs],
    FILTER ( ALL ( Incident ), Incident[date] <= MAX ( Incident[date] ) )
)

    Regards,