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.
Hello to the community!
Maybe someone can help with this:
Two Tables with Relations.
First: Available Capacity
Second: booked Capacity
When I create a Chart and drill down, I always see the total available Capacity (1000):
What I need is: When I drill down the chart, I need the total available Capacity per Quarter (Total Capacity/4), per Month (Total Capacity/12) etc.
Here ist the Data
Sounds easy, but I have no idea.
Thanks and regards,
Michael
Solved! Go to Solution.
Hi @Rygar
You can add a date table to the model and have separate Year/Quarter/Month columns in it. Use these manually created columns on axis in the chart.
Then create below measure. Do not change the order of Month/Quarter/Year. Use this measure as Line value in the chart.
Available =
SWITCH (
TRUE (),
ISINSCOPE ( 'Date'[Month] ), SUM ( 'Available Capacity'[Capacity] ) / 12,
ISINSCOPE ( 'Date'[Quarter] ), SUM ( 'Available Capacity'[Capacity] ) / 4,
ISINSCOPE ( 'Date'[Year] ), SUM ( 'Available Capacity'[Capacity] )
)
Result
I attached a demo pbix below. Hope it helps.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Hi @Rygar
You can add a date table to the model and have separate Year/Quarter/Month columns in it. Use these manually created columns on axis in the chart.
Then create below measure. Do not change the order of Month/Quarter/Year. Use this measure as Line value in the chart.
Available =
SWITCH (
TRUE (),
ISINSCOPE ( 'Date'[Month] ), SUM ( 'Available Capacity'[Capacity] ) / 12,
ISINSCOPE ( 'Date'[Quarter] ), SUM ( 'Available Capacity'[Capacity] ) / 4,
ISINSCOPE ( 'Date'[Year] ), SUM ( 'Available Capacity'[Capacity] )
)
Result
I attached a demo pbix below. Hope it helps.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Hi v-jingzhang,
that's exactly I was looking for! Cool trick with the Date-Table.
Thank you very much for the clear solution and for the very good example!
Best Michael
@Rygar Are the tables related? If so, which direction is the relationship?
Hey Greg,
yes, the tables are related 1:N (Dimension table : Fact Table)
Best Michael
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 |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |