Measure with summarize needs extra bounding

[Jacekk111]

Hello Community,

I have problem with achive correct result in my measure.

I have this type of relations in my table. This is bouding by Date+Shift time (6:00, 14:00 and 22:00).

In visualisation, I apply the filter per day. What I have to do in my measure is reduce the sum of human resources by the time of breakdown during the shift. For example, breakdown tooks 60 minutes and the time of total shift is 480 minutes, so I need to do math (60/480) x 11 people. In my cause I should get 9 hours but, for some reason the  output is 26 hours.

Why 26h? Because it is summing for all 3 shifts (1 shift: 11 people, 2 shift 11 people, 3 shift: 9 people). 

In correct way it should take only 1 shift. 

Basically, I used summarize and it should do math for each shift on daily basises and make 0 where there is nothing bacuse of multiplier and don't know why get 26h instead of 9h. 

3_3 wyjatki = SUMMARIZE( VALUES(cr6af_daneobecnosc[Data Zmiana]) ,"Total",CALCULATE(SUM(cr6af_daneobecnosc[Czas])*SUM(cr6af_wyjatki[Czas])/480))

Do You have idea for this measure?

JP

[rubayatyasmin · Accepted solution]

Hi there,

Based on your description and the provided images, it seems like you want to calculate the total reduction in human resources due to breakdowns during each shift and then summarize it on a daily basis. To achieve this, you can follow these steps:

1. Create a calculated column to calculate the proportion of time lost due to breakdowns during each shift.

2. Create a measure to calculate the reduction in human resources for each row (shift) based on the calculated proportion from step 1.

3. Summarize the measure on a daily basis to get the final result.

Here's how you can do it:

Step 1: Create a calculated column to calculate the proportion of time lost due to breakdowns during each shift:

ShiftDuration = 480
ShiftBreakdownProportion = DIVIDE(SUM(cr6af_wyjatki[Czas]), [ShiftDuration])

Step 2: Create a measure to calculate the reduction in human resources for each row (shift) based on the calculated proportion from step 1:

ReductionInHumanResources = SUM(cr6af_daneobecnosc[Czas]) * [ShiftBreakdownProportion]

Step 3: Summarize the measure on a daily basis to get the final result:

TotalReductionInHumanResources =
SUMX(
VALUES(cr6af_daneobecnosc[Data Zmiana]),
[ReductionInHumanResources]
)