Percentage by multiple categories

[cyn_delin]

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!

[v-yadongf-msft · Accepted 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.

[cyn_delin]

Hi @v-yadongf-msft,

Thank you so much for your help, worked like a charm!

[amitchandak]

@cyn_delin , First get the column in Table and use that or

example

Capacity = maxx(filter(Capacity, database[Month]=Limit[Month]) , Limit[Capacity])