Bar chart split by age and total

[singarfield]

Hi everyone, 

At the moment I have this bar chart, but I would like to create an additional column where I get all the responses in a stacked bar, aggregated by age. How would I go about doing this?

Thank you

Sara

[v-yuezhe-msft · Accepted solution]

@singarfield,

You can create a measure using similar DAX as below. Then use Line and stacked column chart to dispaly the data, or drag the measure to tooltips of your current bar chart.

Measure = CALCULATE([yourmeasure],ALLEXCEPT(Table,Table[Age]))

Regards,
Lydia

[v-yuezhe-msft · Accepted solution]

@singarfield,

You can create a measure using similar DAX as below. Then use Line and stacked column chart to dispaly the data, or drag the measure to tooltips of your current bar chart.

Measure = CALCULATE([yourmeasure],ALLEXCEPT(Table,Table[Age]))

Regards,
Lydia

[singarfield]

Hi, 

Im still very new to power BI, so this doesn't really tell me where I would go to create this value. Could you give more of a step-by-step answer, please?

Thank you

Sara

[v-yuezhe-msft · Accepted solution]

@singarfield,

Right-click your table, and select "New Measure", then apply above formula.


Regards,
Lydia

[singarfield]

Thank you!

Sara

[AkhilAshok]

I don't think you have an option to add Grand Total in a bar chart. A workaround would be the following:

1. Create a new dimension table Age:

Age New = 
UNION (ROW("Age", "Total"), VALUES(Table[Age]))

2. Create a relationship between the Age column in this new dimension table and your fact table.

3. Create a new measure:

Measure New =
SWITCH (
    SELECTEDVALUE ( 'Age New'[Age] ),
    "Total", CALCULATE ( [Measure], ALL ('Age New'[Age]) ),
    [Measure]
)

4. Use this Measure New and Age New dimension in your chart.