cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ujjwalkc
New Member

Additional average bar on a clustered bar chart

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!

2 REPLIES 2
FreemanZ
Community Champion
Community Champion

hi @ujjwalkc 

supposing your table looks like:

StateSubUrg0-1818-6060+
AA1102030
AA2100200300
BB1123

 

try to:

1) unpivot it to a table like:

FreemanZ_0-1683870714737.png

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:

FreemanZ_1-1683870911154.png

FreemanZ_2-1683870927937.png

FreemanZ_3-1683870941193.png

 

 

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!

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors
Top Kudoed Authors