Power Pivot Measure If Statement giving incorrect value

[misstoffeepenny]

Hi all,
I have a measure if statement to calculate the AAFR based on the different companies for YTD. When I set the filter to 'Maine Group' it calculates the correct value, when I select 'Smyth Steel' it calculates the correct value however if I don't filter the data I get a value approx. 10x the correct value. Can anyone help me debug my formula?

NB all my relationships are correct in my data model, it's just the math that isn't working. Also note these are the only 2 current companies in the company table.

formula:
IF(
MAX('Company'[Company]) = "Maine Group",
CALCULATE(
(COUNT('Ext Accident Log'[Injury Type]) / AVERAGE(EmployeeCount[Maine Employee Count]) * 100000),
DATESYTD('Calendar'[Date])
),
IF(
MAX('Company'[Company]) = "Smyth Steel",
CALCULATE(
(COUNT('Ext Accident Log'[Injury Type]) / AVERAGE(EmployeeCount[Smyth Steel Employee Count]) * 100000),
DATESYTD('Calendar'[Date])
),
CALCULATE(
(COUNT('Ext Accident Log'[Injury Type]) / AVERAGE(EmployeeCount[Total Employee Count]) * 100000),
DATESYTD('Calendar'[Date])
)
)
)

 

[RoryA]

Hard to say without any idea what your data looks like, but I'd probably start by checking that the average employee count is what you expect.

 

[misstoffeepenny]

Hard to say without any idea what your data looks like, but I'd probably start by checking that the average employee count is what you expect.

thanks Rory, when I take the last if out as a separate measure I get the correct value but for some reason when its in the combined If it gives me the correct value so I know the Averages are right.

 

[RoryA]

Ah, if you have no filter on Company, then you're actually getting the second calculation done (since Smyth Steel is 'larger' alphabetically than the other item), not the third. You probably need to nest the whole lot in something like:

IF(HASONEVALUE('Company'[Company]),do company specific bits here, do global calc here)

 

[misstoffeepenny]

thank you I had to put the global calc into the if statement twice as the 'false' part but it is now working thank you.

=IF(
HASONEVALUE('Company'[Company]),
IF(
MAX('Company'[Company]) = "Maine Group",
CALCULATE(
(COUNT('Ext Accident Log'[Injury Type]) / AVERAGE(EmployeeCount[Maine Employee Count]) * 100000),
DATESYTD('Calendar'[Date])
),
IF(
MAX('Company'[Company]) = "Smyth Steel",
CALCULATE(
(COUNT('Ext Accident Log'[Injury Type]) / AVERAGE(EmployeeCount[Smyth Steel Employee Count]) * 100000),
DATESYTD('Calendar'[Date])
),
CALCULATE(
(COUNT('Ext Accident Log'[Injury Type]) / AVERAGE(EmployeeCount[Total Employee Count]) * 100000),
DATESYTD('Calendar'[Date])
)
)
),
CALCULATE(
(COUNT('Ext Accident Log'[Injury Type]) / AVERAGE(EmployeeCount[Total Employee Count]) * 100000),
DATESYTD('Calendar'[Date])
)
)