Forum Discussion
Conditional percentage based on aggregate result of multiple columns
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!
4 Replies
- Vvelarde
Community Champion
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:
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
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.
