cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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:

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?

EG

1 ACCEPTED SOLUTION
Community Support

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.

9 REPLIES 9
Super User

Hi,

You can use ALLSELECTED to achieve this:

100% = divide(MAX('Table'[Value]),CALCULATE(sum('Table'[Value]),ALLSELECTED('Table'[type])))

Example:

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!

Proud to be a Super User!

New Member

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

Community Support

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.

New Member

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

Community Support

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.

New Member

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

Community Support

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.

New Member

Hi Stephen @v-stephen-msft ,

Yes, it's connected by the ID column. Please see screenshots.

Thanks!!

EG

Community Support

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.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors