Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
egnhc
New Member

Show percentage by column/row instead of %GT

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:

pbi table.PNG

desired.PNG

 

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

1 ACCEPTED SOLUTION

Hi @egnhc ,

 

Here's my solution.

The relationship is as follows.

vstephenmsft_0-1645681591570.png

 

1.Create a calculated column in the Outcome table.

Month = FORMAT(RELATED('Date'[Date of Call]),"MMM")

vstephenmsft_1-1645681663923.png

 

2.Create a measure.

Measure = DIVIDE(COUNT('Outcome'[ID]),CALCULATE(COUNT(Outcome[ID]),ALLEXCEPT(Outcome,Outcome[Month])))

 

3.Create the chart visual as follows.

vstephenmsft_2-1645681735480.png

 

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.

View solution in original post

9 REPLIES 9
ValtteriN
Super User
Super User

Hi,

You can use ALLSELECTED to achieve this:

100% = divide(MAX('Table'[Value]),CALCULATE(sum('Table'[Value]),ALLSELECTED('Table'[type])))
 
Example:
ValtteriN_0-1643739951207.png
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

 







Did I answer your question? Mark my post as a solution!

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.

vstephenmsft_1-1644392019877.png

 

 

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])

 

vstephenmsft_2-1644392074996.png

vstephenmsft_3-1644392087942.png

 

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):

Outcome count = count(Outcome[Outcome Type])
Outcome total =
CALCULATE([Outcome count],'Outcome'[Outcome Type] IN { "Caller declined services" })+
CALCULATE([Outcome count],'Outcome'[Outcome Type] IN { "Follow-up call needed" })
% Outcome Type = DIVIDE('Outcome'[Outcome count],'Outcome'[Outcome total],0)
 

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.

vstephenmsft_0-1644480546706.png

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. 

dummy chart.PNGdate.PNGoutcome.PNG

 

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. 

 

relationship.PNGrelationship 2.PNG

 

Thanks!!

EG

Hi @egnhc ,

 

Here's my solution.

The relationship is as follows.

vstephenmsft_0-1645681591570.png

 

1.Create a calculated column in the Outcome table.

Month = FORMAT(RELATED('Date'[Date of Call]),"MMM")

vstephenmsft_1-1645681663923.png

 

2.Create a measure.

Measure = DIVIDE(COUNT('Outcome'[ID]),CALCULATE(COUNT(Outcome[ID]),ALLEXCEPT(Outcome,Outcome[Month])))

 

3.Create the chart visual as follows.

vstephenmsft_2-1645681735480.png

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.