Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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:
The model
Then, the report:
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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
7 | |
6 |
User | Count |
---|---|
15 | |
13 | |
11 | |
9 | |
8 |