topic Re: Additional average bar on a clustered bar chart in DAX Commands and Tips

Additional average bar on a clustered bar chart

ujjwalkc — Thu, 11 May 2023 05:32:26 GMT

I have a dataset for Australia for various variables. The data is at suburb label with data on states as well. I have created a clustered bar chart in powerbi for all the variables for a selected suburb. I want to add bars for average values in overall Australia and the state of the selected suburb as well. How do i do this in powerbi?

Example DataSets:

SubUrb | State| Age under 18 | age 18-60 | age 60+

ABC |DEF | 10 | 20 | 30

Steps and demonstration highly welcome!

Re: Additional average bar on a clustered bar chart

FreemanZ — Fri, 12 May 2023 05:57:04 GMT

hi ujjwalkc

supposing your table looks like:

State	SubUrg	0-18	18-60	60+
A	A1	10	20	30
A	A2	100	200	300
B	B1	1	2	3

try to:

1) unpivot it to a table like:

About unpivot: https://learn.microsoft.com/en-us/power-query/unpivot-column

2) plot a slicer with the suburb column

3) plot a clustered column chart with necessary columns and feed two measures as constant line like:

StateAvg = CALCULATE( AVERAGE(data[Count]), ALL(data), data[State]=MAX(data[State]) )

CountryAvg = CALCULATE( AVERAGE(data[Count]), ALL(data) )

it worked like:

Re: Additional average bar on a clustered bar chart

ujjwalkc — Fri, 12 May 2023 06:04:00 GMT

Thanks so much! But, I wanted the values for the state and the country as an additional bar in the clustered bar chart. On top of that, I want the legend to have the selected suburb, state and Australia as legend items. CHeers!