The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hello all,
Total newbie here, I apologize if the title is not explicative enough.
I have two tables: DataBase and Limit
'DataBase'
Machine | Month | Performance |
A | April | 80 |
B | April | 50 |
C | April | 30 |
A | May | 60 |
B | May | 90 |
C | May | 30 |
'Limit'
Month | Capacity |
April | 100 |
May | 150 |
'Limit' shows that all machines, A, B and C, have the same capacities (let's say, the maximum hours of work), but this capacity changes by month. On the other hand, 'DataBase' shows the actual performance (hours) of all machines on all months.
I've been trying to get the percentage of performance based on month and machine. To put it more clearly:
Machine | Month | (calculation in my head) | Expected result (% performance) |
A | April | 80/100 | 0,8 |
A | May | 60/150 | 0,4 |
B | April | 50/100 | 0,5 |
B | May | 90/150 | 0,6 |
C | April | 30/100 | 0,3 |
C | May | 30/150 | 0,2 |
I've been messing around with the quick measure:
Performance divided by Capacity =
DIVIDE(SUM('DataBase'[Performance]), SUM('Limit'[Capacity]))
But clearly I don't know what I'm doing because it doesn't work the way I want.
Any help is appreciated!
Solved! Go to Solution.
Hi @cyn_delin ,
Please create a new column:
Performance divided by Capacity =
var per = 'DataBase'[Performance]
var cap = CALCULATE(MAX('Limit'[Capacity]),FILTER('Limit','Limit'[Month] = 'DataBase'[Month]))
return
DIVIDE(per,cap)
Best regards,
Yadong Fang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @cyn_delin ,
Please create a new column:
Performance divided by Capacity =
var per = 'DataBase'[Performance]
var cap = CALCULATE(MAX('Limit'[Capacity]),FILTER('Limit','Limit'[Month] = 'DataBase'[Month]))
return
DIVIDE(per,cap)
Best regards,
Yadong Fang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@cyn_delin , First get the column in Table and use that or
example
Capacity = maxx(filter(Capacity, database[Month]=Limit[Month]) , Limit[Capacity])
User | Count |
---|---|
28 | |
12 | |
8 | |
7 | |
5 |
User | Count |
---|---|
36 | |
14 | |
12 | |
7 | |
7 |