Running total of a measure

[SidM]

Hello,

I needed some help regarding getting the cumulative fail rate formula. I have the following table below.

 I have been able to calculate the Running Total, Total Population, Population Maturity @ Age_Combined and Instantaneous fail rate correctly in Power BI.

However I havent been able to correctly come up with an expression in DAX to correctly calculate Cumulative Fail Rate.

I have read through numerous forums and tried various expressions with no luck. The screenshot below from Power BI shows one of the expressions I tried for Cumulative Fail Rate with no luck and hence the incorrect behaviour for that measure.

The screenshot below shows the correct behaviour in the Cumulative Fail Rate Column.

 Interestingly the waterfall chart using the Instantaneous fail rate by Age_Combined shows the behaviour I expect. However I need it in a table format.

 Any help on getting the correct Cumulative Fail Rate behaviour using a measure will be appreciated.

Thanks.

I have used the following measures.

Running Total =
CALCULATE(COUNTA('Table1'[Age_Combined]), FILTER(ALLSELECTED('Table1'[Age_Combined]),
ISONORAFTER('Table1'[Age_Combined], MAX('Table1'[Age_Combined]), ASC)))

Total Population = CALCULATE(COUNTA('Table1'[ID]), ALL(Table1))

Population Maturity @ Age_Combined = DIVIDE([Running Total],[Total Population])

Instantaneous fail rate = DIVIDE(COUNTA('Table1'[Age_Fault]),[Running Total])+0

Cumulative Fail Rate =
SUMX (FILTER (ALL ( Table1[Age_Combined]),
Table1[Age_Combined] <= Table1[Age_Combined]), [Instantaneous fail rate])

Regards,

Sid.

[PSBR]

RunningTotal = CALCULATE(SUM(Orders[Sales]),
                         FILTER(ALL(Orders),
                         Orders[Order Date] <= MAX(Orders[Order Date])))

// You can achiv running total by using above formula. In this formula "Orders" is table and "Orders[Sales]" & "Orders[Date]" are the       columns. 

[v-cherch-msft]

Hi @SidM

You may refer to below post and use SUMMARIZE Function and SUMX Function. If you need further help, please share your data sample file. You can upload it to OneDrive or Dropbox and post the link here.

https://community.powerbi.com/t5/Desktop/Cumulative-Total-of-Cumulative-Totals/td-p/367446

Regards,

Cherie

[SidM]

Hi @v-cherch-msft,

I have uploaded the file to one drive. Below is the link.

https://1drv.ms/u/s!ApZEQFj8nXj8gQENGpxr1-vmEpwz

I did also try the summarize function mentioned in the post you sent me, however I did not have any success.

Thanks for the help.

Regards,

Sid.

[v-cherch-msft]

Hi @SidM

It seems you may try to change your Running Total measure instead of using quick measure. Below is the similar post for your reference.

https://forum.enterprisedna.co/t/cumulative-sum-of-a-measure-that-creates-another-cumulative-sum/739...

Regards,

Cherie

[SidM · Accepted solution]

Hi @v-cherch-msft,

Thanks for the informative post.

As Sam in his post states “The key to understanding these is to try to break down each table and the filters.”, I was able to figure out that the model column was causing my cumulative totals to not be calculated correctly.

I changed my measures for Running Total and Total Population by excluding the model column and got the intended behaviour with the cumulative total.

Running Total = CALCULATE(
COUNTA('Table1'[Age_Combined]),
FILTER(ALLEXCEPT(Table1,Table1[Model]),
ISONORAFTER('Table1'[Age_Combined], MAX('Table1'[Age_Combined]), ASC)))

Total Population = CALCULATE(COUNTA('Table1'[ID]), ALLEXCEPT(Table1,Table1[Model]))

Instantaneous fail rate = DIVIDE(COUNTA('Table1'[Age_Fault]),[Running Total])+0

Cumulative Fail Rate = SUMX (FILTER (ALLSELECTED((Table1[Age_Combined])),
'Table1'[Age_Combined] <= MAX('Table1'[Age_Combined])), [Instantaneous fail rate])

Below is the link to the file with the correct measures.

https://1drv.ms/u/s!ApZEQFj8nXj8gQLBQFNfPVCM-dLC

Regards,

Sid.