Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I would like to measure to what extend items are available for a certain project within certain types of storage locations. I’ve got two fact tables (‘Required items per project’ and ‘Available items’) and two filter tables (‘Item’ and ‘Location’). For example, P1 requires 25x item 001 and there are 25 items available (100%) organization-wide. However, as our users would like to be able to filter on several specific combinations of locations to deliver items, dynamic filters are required. In case of only counting the available material with type of storage set at User (locations A,B, D, F) for project P1, the graphs should show 17/25 = 68% availability.
So far, we’ve only succeeded in doing this with predetermined filters and merged tables in the powerquery. But as our list of locations is far longer than 6 and doing this with merged tables would result in an enormous amount of columns, we would like to see if it’s possible to use a filter on the board’s main screen for this operation. Could you please help us with the required steps to achieve this?
We're now looking at the following steps:
- Create dynamic table, based on on-screen location filter;
- Create SUM measures (or column) for availabilty per item, based on aforementioned dynamic table;
- Calculate availability (Available items / required items).
Kind regards,
Stephan
Here's the full solution.
First, the model:
Then, the report:
And here are the measures:
# Available Items = SUM( 'Item Availability'[Item Count] )
# Items Needed = SUM( 'Projects'[Item Count] )
Availability =
var __itemsNeeded = [# Items Needed]
return
if( __itemsNeeded > 0,
var __itemsAvailable = [# Available Items]
var __result =
if( __itemsAvailable >= __itemsNeeded,
1,
DIVIDE( __itemsAvailable, __itemsNeeded )
)
return
__result + 0
)
Best
D
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
20 | |
18 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |