cancel
Showing results for
Did you mean:
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:

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
Super User

Hi,

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

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
8 REPLIES 8
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

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.

Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
Check out my latest book!

Helper V

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 ID Group 12345 a 1247823 b 332434 a 1243699 b 2483797 a 4543522 a

sample data from Events table:

 client id event id event 12345 5522 event 1 1247823 4669 event 2 332434 5543 event 3 4543522 6466 event 4 4543522 1122 event 1 2483797 8899 event 3 2483797 9898 event 4 1243699 7321 event 1
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper V

@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

Super User

Hi,

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

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper V

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

Helper V

Thank you so much 🙂

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### 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.