Stuck- How to go about developing this clustered column chart- open to ideas/solutions/insights.

[joshmahe]

I am relatively new to Power BI but need to get this chart done asap, so any help in really appreciated. I have the below chart- which is showing the Average and Total number of Employees by their Tier. Tier has values - EEonly, EESpouse, EEFamily, EEChildren. The average and total of these employees belonging to these tiers is calculated based on the year. The chart i have attached was developed in excel but i need to develop the same in powerbi. 

Here are few things I need help with- 

1. I need to filter this chart by the average and total, so I need average and total in the slicer and based on what i choose, the chart shows the average or the total. Help needed on how to go about the slicer. 

2. If i use a measure to calculate EEonly or EESpouse or other tiers, how can i go about it? Using a measure for each tier and each year will give me 8 measures for Average and 8 measures for Total. I dont know if i am thinking about the entire thing wrongly and if it can be done in a much easier way. And if i use measures, I am also confused how to go about including them in field parameters or switch/if because i would need to choose multiple measures at the same time. 

3. If i use calculated columns instead, i wont be able to control them with slicers. And also confused about how many calculated columns i need to develop to achieve what i want. 

My data looks like this- 

Year	EmployeeTier
2021	EmployeeChildren
2021	EmployeeChildren
2021	EmployeeChildren
2021	EmployeeFamily
2021	EmployeeFamily
2021	EmployeeFamily
2021	EmployeeSpouse
2021	EmployeeSpouse
2021	EmployeeSpouse
2021	EmployeeChildren
2021	EmployeeChildren
2021	EmployeeChildren
2022	EmployeeFamily
2022	EmployeeFamily
2022	EmployeeFamily
2022	EmployeeSpouse
2022	EmployeeSpouse
2022	EmployeeSpouse
2022	EmployeeFamily
2022	EmployeeFamily
2022	EmployeeSpouse
2022	EmployeeSpouse

The calc for average and total would just count the number of entries for each year and each tier. Average would be count divided by number of months in that year, and total would be just total per year. 

Any help is appreicated, thanks in advance. 

[miTutorials]

Create a measure for Total and Average.  Create a Clustered column chart and add Employee Tier in X Axis , Year in the legend and create a what if parameter and use that measure in the Y Axis.

Please see the tutorial below if you don't know how to create what if parameter.

New Feature: Slicer to choose which columns to display on chart using Fields Parameter in PowerBI

Best regards,

Ismail | MiTutorials

🎥 MiTutorials on YouTube
Explore over 200+ Power BI tutorials and enhance your skills!