How to show distribution % by legend on a lines chart in PowerBI?
Hi guys, PowerBI is easy to learn but a bit complex visual requirements sometimes trouble us! Let’s see the story of Jack!
Jack was a new employee in the XYZ organization. His manager itself took his interview in one of the rounds. He mentioned PowerBI visualization skills in his resume and marked them as his top skill. When he newly joined the organization, his manager gave him work to replicate a Tableau dashboard in PowerBI. He was very excited to complete that work on time and to impress his Boss.
He was been provided the following data. You can find this data ‘Wildlife Population’ at my Github repository link.
His objective is to get the below values- notice the year-wise gender and genderwise yearly distribution female-male columns.
- year-wise gender distribution for the year 2012 for females = 386/522 = 73.95%
- gender-wise yearly distribution for the year 2012 for males = 136/1227 = 11.08%
Below is the chart which he was trying to develop. Isn’t it a simple line chart? — Of course yes, but the calculations are different.
He started by importing the data into PowerBI and validated import with a table icon with the above four column names.
Selected the line chart visual and made the highlighted selections and kept the Data labels ‘On’.
But that didn’t serve his purpose.
He created a ‘Measure’ and replaced ‘Count’ under ‘Values’ by measure and converted it into %. 😀
genderwise_% = SUM(‘Wildlife population’[Count]) / CALCULATE(SUM(‘Wildlife population’[Count]), ALLSELECTED(‘Wildlife population’[Year]))
Note — If you replace ‘Year’ with ‘Gender’ then it will give you a year-wise gender distribution being year on x-axis.
yearwise_% = SUM(‘Wildlife population’[Count])/CALCULATE(SUM(‘Wildlife population’[Count]),ALLSELECTED(‘Wildlife population’[Gender]))
The difference between the two is —
Genderwise yearly distribution— It will sum up to 100 i.e., it gives you the percentage gender-wise distribution of counts by the years.
Year-wise gender distribution — It will sum up to 100 i.e., it gives you the percentage yearly distribution of counts by the gender.
____________________________________________________________
Thanks for reading ❤
For any suggestions or queries, leave your comments below and follow for updates.
If you liked the article, please hit the 👏 icon to support it. This will help other Medium users find it. Share it, so that others can read it!
Happy Learning! 😊
