Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi everyone,
I'm trying to make a clustered column chart to show some data by category by month.
My values are count of text value. PBI only has defualt calculation of show data in %GT.
But I'd like to show the % by column, (I made below matrix in PBI and had month on the columns, but it should be same idea if I had month on rows), so the per month data would add up to 100%. Please see the matrix and a desired chart made in Excel below:
I was trying to make some measures to calculate the %, but I'm having trouble to get a total count of all categories for one month. Whenever I had the Axis added (which will show the categories on chart), it will give me the value of each bar of 100%.
Any suggestions on the mearsures please?
Thanks very much in advance!
EG
Solved! Go to Solution.
Hi @egnhc ,
Here's my solution.
The relationship is as follows.
1.Create a calculated column in the Outcome table.
Month = FORMAT(RELATED('Date'[Date of Call]),"MMM")
2.Create a measure.
Measure = DIVIDE(COUNT('Outcome'[ID]),CALCULATE(COUNT(Outcome[ID]),ALLEXCEPT(Outcome,Outcome[Month])))
3.Create the chart visual as follows.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
You can use ALLSELECTED to achieve this:
Proud to be a Super User!
Hi ValtteriN,
Thanks for your suggestion. However my values are count of text/string. Some functions like SUM doesn't work on this type of data. I tried to add some COUNTX calculation to it but couldn't get it straightened out.
Do you have any suggestions with text values?
Thanks again!
EG
Hi @egnhc ,
I created dummy data and then used measures to calculate percentages.
1.Create the following three metrics separately.
Count = COUNT('Table'[Outcome Type])
Count by Month = CALCULATE(COUNT('Table'[Outcome Type]),FILTER(ALLSELECTED('Table'),[Month]=MAX('Table'[Month])))
Percentage = DIVIDE([Count],[Count by Month])
You can check more details from the attachment.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Stephen @v-stephen-msft
Thank you for the suggesttion!
However, I'd like to have the X Axis to show outcome type instead of month. I had a picture in the original post that I made in Excel to show the desired chart. The purpose is to see/compare the data over months with different outcome type.
I was able to create measures that manually add up the counts for outcome type (I only listed two outcome types here as example):
But the issue with this calculation is that my data source potentially will be updated so the outcome type name will be changed. Then the Outcome total measure will need to be manually updated to the new name.
Is there a way to create measures by outcome type so I can drag Outcome Type to the Axis when creating the bar chart?
Thanks!
EG
Hi @egnhc ,
If you want the axis to show the Outcome Type, then you swap it with the month.
I don't think you need to make the formula static, like 'Outcome'[Outcome Type] IN { "Caller declined services" }.
My formula is a dynamic, have you tried to modify it according to my formulas?
If you are still in doubt, please provide your dummy data and expected results.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Stephen @v-stephen-msft ,
Yes I tried your measures but it didn't come out as expected.
I forgot to mention that my date/month table and outcome type table are two different tables. I don't know if that complicates the issue. (Maybe it changes everything?? Sorry!!)
There isn't an option for me to upload the dummy data, so I attached some screenshots for the chart and my two tables.
Right now the bar chart is showing 100% for all the bars.
The desired chart would be the bar to show % as the matrix indicated (e.g. the Jan bar for Caller declined services would show 15.38%).
Thanks again!!
EG
Hi @egnhc ,
Is the relationship between the two tables connected by the ID column?
What is the relationship?
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Stephen @v-stephen-msft ,
Yes, it's connected by the ID column. Please see screenshots.
Thanks!!
EG
Hi @egnhc ,
Here's my solution.
The relationship is as follows.
1.Create a calculated column in the Outcome table.
Month = FORMAT(RELATED('Date'[Date of Call]),"MMM")
2.Create a measure.
Measure = DIVIDE(COUNT('Outcome'[ID]),CALCULATE(COUNT(Outcome[ID]),ALLEXCEPT(Outcome,Outcome[Month])))
3.Create the chart visual as follows.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
89 | |
83 | |
75 | |
49 |
User | Count |
---|---|
142 | |
140 | |
110 | |
69 | |
55 |