topic Trying to calculate adherence with the plan output vs the actual output in DAX Commands and Tips

Trying to calculate adherence with the plan output vs the actual output

Anonymous — Mon, 14 Aug 2023 18:38:59 GMT

Hi everyone,

I need some help trying to calculate the adherence of the plan with the actual output. This is an example of what i've been trying to do in powerbi.

3500	13.48%	4,080	11.25%
7100	27.35%	6866	26.45%
15360	59.17%	13299	51.23%
	100.00%		88.93%

the second column perform the following calculation A1/SUM(A$1:A$3). basically is a division between whatever is in the A cell and the total amount of the A column.

and the 4th column calculate the following ecuation IF((D2/B2)<=1,(D2/B2)*C2,(1-((D2/B2)-1))*C2).

I create the three following measure

aver%output =

SUM('MFG QA Man (2)'[Actual Output])/ SUM('MFG QA Man (2)'[Plan Output])

TotalVolume =

DIVIDE(SUM('MFG QA Man (2)'[Plan Output]), CALCULATE(SUM('MFG QA Man (2)'[Plan Output]), ALLSELECTED('MFG QA Man (2)')))

Adherencia =

VAR Porcentaje = ('MFG QA Man (2)'[aver%output])

VAR Resultado =

SWITCH(TRUE(), Porcentaje <= 1, Porcentaje * 'MFG QA Man (2)'[TotalVolume], Porcentaje > 1, (1 - (Porcentaje - 1)) * 'MFG QA Man (2)'[TotalVolume])

RETURN Resultado

The problem is when the power bi perform the calculation of the adherence its doing the sum of the total adherence wrong

the total should be 88.93% instead of 93.39% and i dont know what else to do.

PS. Sorry for bad english

Re: Trying to calculate adherence with the plan output vs the actual output

Alef_Ricardo_ — Wed, 16 Aug 2023 01:25:49 GMT

Hi Rosarie9, welcome to the community! 😊

It seems like you're trying to calculate the adherence of the plan with the actual output using Power BI. From your description, it looks like you have created three measures: `aver%output`, `TotalVolume`, and `Adherencia`. However, the total adherence calculated by Power BI is not matching your expected result.

One possible solution to this issue could be to check the calculations used in your measures and make sure they are correct. You could also try breaking down the calculations into smaller parts to see if each part is returning the expected result.

It seems like you have created three measures: `aver%output`, `TotalVolume`, and `Adherencia`. However, the total adherence calculated by Power BI is not matching your expected result.

Another approach could be to use the `CALCULATE` function in Power BI, which allows you to perform calculations on a filtered version of your data. You can find more information about the `CALCULATE` function in this [DAX tutorial].

If you're still having trouble, you could try posting your question on the [Microsoft Fabric Community] or [Power BI Community] forums, where other users might be able to help you with your specific issue.

I hope this helps! Let me know if you have any further questions. 😊

Re: Trying to calculate adherence with the plan output vs the actual output

Anonymous — Wed, 16 Aug 2023 02:30:41 GMT

Hi Anonymous ,

It seems that the sum of the MEASURES is not correct.

Please create measures based on the Adherencia.

VALUE_ = VAR _a = 'table'[Adherencia] VAR _b = SUMMARIZE ( 'table', 'table'[date], "aaa", 'Product'[Adherencia] ) RETURN IF ( ISINSCOPE ( 'table'[date] ), _a, SUMX ( _b, [aaa] ) )

Or you can change the 'table'[date] to the unique value in the table like the index.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.