Conditional percentage based on aggregate result of multiple columns

[EduardoSanti]

Hi all, I am new in Porwe BI and I do not know how to handle with percentage based on aggregate and conditions. This is my model in Excel:

First, I have to know which sizes I have achieved the target (Plan x Result).

After that, I have to group by inch, and check how many sizes have achieved, and how many have not.

Finally, I have to know the percentage of achievement, based on planned and resul, for each inch. This is how the SQL is:

This is SQL result, exactly what I need in Power BI:

I am afraid I was not clear enought. I would appreciate at least a direction to understand how can I handle this.

Thank you in advance!

[Vvelarde]

@EduardoSanti

Hi, create 3 measures:

OK =
CALCULATE ( COUNT ( Table1[Inch] ), Table1[Plan Achieved] = "Yes" )

NOK =
CALCULATE ( COUNT ( Table1[Inch] ), Table1[Plan Achieved] = "No" )

Achievement =
DIVIDE ( [OK], CALCULATE ( COUNT ( Table1[Inch] ), VALUES ( Table1[Inch] ) ) )

Insert a Table Visual and:

[EduardoSanti]

@Vvelarde, thank you very much for your support. I understand your purpose, but there is one detail: Each size, that has an Inch associated, has its own Plan and Result, and I must know how many sizes of each Inch has achieved the Result.

As I am doing SUM(Result) - SUM(Plan) > 0 = Achieved, the result is different if I put as dimension only Inch, or Inch and Size.

Check this:

On your suggestion, I guess R14 would be "Not achieved". For my need, I should know OK = 3, NOK = 7. Am I right?

Thank you in advance!

[Vvelarde]

@EduardoSanti

A few question:

The plan Achieved for a SizeCode  depends if Result is greater or equal to Monthly Plan?

The plan Achieved for an Inch (R14) depends on Quantity of Yes vs Quantity of No in sizeCode?

If Yes >= No  Plan then Yes else No. Is correct?

The %Achievements is Count of Yes vs Total?

Don't understand the way that you want yo show in the matrix.

[EduardoSanti]

@Vvelarde, thank you for your interest. Please find below the answer for your questions:

The plan Achieved for a SizeCode  depends if Result is greater or equal to Monthly Plan?

Yes, If the Result of SizeCode is equal or greater than the Plan, means the SizeCode achieved the target,

The plan Achieved for an Inch (R14) depends on Quantity of Yes vs Quantity of No in sizeCode?

Yes, if I have 10 SizeCode of R14 Inch in a total of 10 SizeCode planned that achieved the target, means 100% of achievement.

If Yes >= No  Plan then Yes else No. Is correct?

It don´t metter if the number of "yes" is bigger than "no". It is important only to know "yes/total", in percentage.

The %Achievements is Count of Yes vs Total?

Exactly.

Don't understand the way that you want yo show in the matrix.

Should be exactly like this:

For example, R14, Yes = 7, so to know the achievement, it is Total Yes / Total, or 7 / 11 = 64%

I hope I was clear enought.

Best regards