Percentage utilization filtered on monthly basis

[mgalat]

Hi Power BI comunity,

I have a problem with the calculation and display of the utilization percentage on a monthly basis. Namely, I collect the inventory balance on a monthly basis, and my capacity is fixed per warehouse. I need that when filtering a certain month, I get the utilization based on the given fixed capacity per warehouse.

Database example:

	Table monthly stock
Plant	Material	UoM	Stock	Date	Warehous
AA	101	TH	1.5	01/01/2024	A1
AA	102	TH	0.5	01/01/2024	A1
AA	103	TH	1	01/01/2024	A2
AB	101	TH	4	01/01/2024	A3
AB	102	TH	3.5	01/01/2024	A3
AB	103	TH	2	01/01/2024	A3
AC	104	TH	6	01/01/2024	A4
AA	101	TH	2.5	01/02/2024	A1
AA	102	TH	4	01/02/2024	A1
AA	103	TH	7	01/02/2024	A2
AB	101	TH	5	01/02/2024	A3
AB	102	TH	3.5	01/02/2024	A3
AB	103	TH	9	01/02/2024	A3
AC	104	TH	11	01/02/2024	A4

	Table warehouse capacity
Plant	Warehouse	Capacity	Total qty	Capacity utilisation %
AA	A1	7	8.5
AA	A2	3	8
AB	A3	10	27
AC	A4	15	17

[v-heq-msft]

Hi @mgalat ,

Here some steps that I want to share, you can check them if they suitable for your requirement.

Here is my test data:

1.Open power query and group by Plant and warehouse

2.Create one to one relationship 

3.Create a measure

utilisation % = SELECTEDVALUE('warehouse capacity'[Capacity])/SELECTEDVALUE('monthly stock'[Sum Stock])

Final output

Best regards,

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[mgalat · Accepted solution]

Hi, problem solved.

I changed the calculation for utilization

Utilisation % = Consolidated[SU Qty 2] /                  SELECTEDVALUE(WHs_Capacity[Capacity])

And in the visualisation I added the sum of qty from the main table.

Now when filtering between months, utilisation % is also updated.