topic Re: Percentage by multiple categories in DAX Commands and Tips

Percentage by multiple categories

cyn_delin — Tue, 06 Sep 2022 20:51:00 GMT

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!

Re: Percentage by multiple categories

amitchandak — Wed, 07 Sep 2022 02:09:41 GMT

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

example

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

Re: Percentage by multiple categories

v-yadongf-msft — Wed, 07 Sep 2022 08:02:27 GMT

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.

Re: Percentage by multiple categories

cyn_delin — Wed, 07 Sep 2022 12:56:10 GMT

Hi v-yadongf-msft,

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