Total capacity per Year, Quarter, Month etc

[Rygar]

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

[v-jingzhang · Accepted 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.

[Rygar]

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

[Greg_Deckler]

@Rygar Are the tables related? If so, which direction is the relationship?

[Rygar]

Hey Greg,

yes, the tables are related 1:N (Dimension table : Fact Table)

Best Michael