Skip to main content
cancel
Showing results for 
Search instead 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

Reply
SidM
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.Capture1.JPG

 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.

Capture4.JPG

 

 

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

 

Capture2.JPG
 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.

 

Capture3.JPG

 

 

 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

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.

View solution in original post

5 REPLIES 5
PSBR
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. 

v-cherch-msft
Microsoft Employee
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.

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.

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.

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors