cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
OPS-MLTSD
Helper V
Helper V

Calculate percentage of total for a category not the grand total

Hello,

 

I have two tables Events and Groups, the common field between these two tables is client ID

 

I want to create a visual that shows the number of events the clients (in group A and B) attended and in the tooltip, I want to show the percentage of events attended.

This is how I am planning on displaying the visual: 

 

OPSMLTSD_1-1631562993496.png

They are basically the same visual repeated 3 times where My Axis is Group and Value is EVENT ID

The first visual labeled group A just has the group A filter applied to the visual and the second visual labeled Group B has group B filter applied to the visual.

There are 4 types of events and when the user hovers over each bar graph, I want to display the percentage of total events attended. I don't want to show the percentage of grand total. So for example, when the user hovers over event 1 of group A, I want them to see 300/1250 = 24%

Likewise when they look at the last bar graph, which is a combination of group A and B, I want them to see 300+(whatever the number is for group B)/2100 = --%

If someone could please help me figure out how I can create this measure to be added to the tooltip section, that would be much appreciated. Thank you

1 ACCEPTED SOLUTION

Hi,

Events attended = DISTINCTCOUNT(Events[event id])
Events attended (%) = DIVIDE([Events attended],CALCULATE([Events attended],ALL(Events[event])))

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

8 REPLIES 8
Greg_Deckler
Super User
Super User

@OPS-MLTSD Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Here is the sample data from each table - as I mentioned, I would like to create a measure which I can add to the tooltip section of the bar graph showing the percentage total within each group.

 

sample data from Group table:

Client IDGroup 
12345a
1247823b
332434a
1243699b
2483797a
4543522a

 

sample data from Events table:

client idevent idevent
123455522event 1
12478234669event 2
3324345543event 3
45435226466event 4
45435221122event 1
24837978899event 3
24837979898event 4
12436997321event 1

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur  unfortunately I am on the October 2020 version of power bi which i why I cannot open your file, I am not able to upgrade my current version due to company policy, if you could please post your calculation here that would be much appreciated. thank you

Hi,

Events attended = DISTINCTCOUNT(Events[event id])
Events attended (%) = DIVIDE([Events attended],CALCULATE([Events attended],ALL(Events[event])))

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

just wondering, what is the difference between ALL, ALLEXCEPT, and ALLSELECTED?

Thank you so much 🙂

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors