cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Running total of a measure

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.

1 ACCEPTED SOLUTION
Frequent Visitor

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.

5 REPLIES 5
Frequent Visitor

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.

Microsoft Employee

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

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.

Microsoft Employee

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors