Running total with measures - filters

[Anonymous]

Hi!

I'm using information from 3 different tables to create this table, that can be sorted by 3 filters (Shop, Part and Region): 

NumberFailures is calculated as:

NumberFailures = CALCULATE(count(Failures[Part]),Failures[PartEnd]<>blank())

TotalComponents is:

TotalComponents = sum(NoComponents[NoComponents])+[NumberFailures]

FailureRate is

FailureRate = [NumberFailures]/[TotalComponents]*100

AcummAvgFailureRate_CalendarYear =

VAR Age = SELECTEDVALUE( Failures[CalendarYear], 0 )

VAR Comp = SELECTEDVALUE(NoMachines[Part])

VAR Result =

sumX(

FILTER(

SUMMARIZE(ALLSELECTED(Failures),NoMachines[Part], Failures[CalendarYear],

"FailureR", [FailureRate] )

,

Failures[CalendarYear] <= Age &&

Comp= NoMachines[Part] ),

[FailureR] )

RETURN

Result

The problem comes when I try to calculate the running total of the failure rate. In the first picture (if only one shop is selected), it is calculated correctly. However, when I select whole Region (UK) the accumulated failure rate is not calculated properly. I have seen that it's because of the TotalComponents. For example, in 2005 there have only been failures from Shop 1  this measure will use only the Number of Components from Shop1, instead of all the shops in UK. So in the table below the failure rate for 2005 is 1.38 but the accumulated one is 2.63 (like in the first picture). How could I solve this?? Here's a link to a sample data (I hope it works because it's the first time try to share it like this) https://app.powerbi.com/groups/me/reports/5ebe6559-38cf-4f85-b3d3-421ffbad2ac9?ctid=100b3c99-f3e2-4d... 

Thanks! 🙂

[technolog · Accepted solution]

From what you've described, the issue arises when you're trying to calculate the running total of the failure rate for an entire region, like the UK. The problem seems to be with the TotalComponents measure, which is only considering the components from a single shop when you filter by a specific year, rather than all the shops in the region.

To solve this, you need to adjust the way you're calculating TotalComponents so that it takes into account all the shops in the selected region, regardless of whether there were failures in that year or not.

One way to do this is to modify the TotalComponents measure to ensure it's summing up the components from all shops in the selected region, rather than just the ones with failures in the filtered year. Here's a possible solution:

Instead of:

TotalComponents = sum(NoComponents[NoComponents])+[NumberFailures]
You might try:

TotalComponents =
CALCULATE(
SUM(NoComponents[NoComponents]),
ALLSELECTED(Shops[Shop]),
VALUES(Shops[Region])
) + [NumberFailures]
This modification to TotalComponents uses ALLSELECTED to ensure that all shops within the selected region are considered, even if they didn't have failures in the specific year you're looking at. The VALUES function ensures that the calculation respects the region filter.

By making this adjustment, when you calculate the running total for the failure rate across a region, it should now consider the components from all shops in that region, giving you a more accurate accumulated failure rate.