cancel
Showing results for
Did you mean:
New Member

percent of grand total

I have the data in groups (age_groups) by year (2015 & 2016). I want percentage by age_group to be based on total / year (e.g. 1462 for 2015) and not by the grand total of 2925.

1 ACCEPTED SOLUTION

The exact formula depends on your table structure. But you need to remove the natural filter that is applied by your age groups without removing the filters on the year. You use the ALL function to remove filters. It could be something like this

but it it does depend on your data structure. Just use ALL to remove the filter from the denominator.

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
13 REPLIES 13

The exact formula depends on your table structure. But you need to remove the natural filter that is applied by your age groups without removing the filters on the year. You use the ALL function to remove filters. It could be something like this

but it it does depend on your data structure. Just use ALL to remove the filter from the denominator.

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Helper I

I think I have a similar need but I am not able to get it quite right. The data summary below from a column chart shows the 'count of appt_id' for each month of a year (created bins for the months) in terms of various appt_status (no-shows, rescheduled, show).  I need to calculate the % of appt_status total for each month. So, for Jan 2016, no-show % = 1675/8835 = 19%, rescheduled = 18.8% and show = 62.2% and so on for each month.

 month no-show rescheduled show Total Jan-16 1675 1664 5496 8835 Feb-16 1612 1600 5716 8928 Mar-16 1525 1700 5698 8923 Apr-16 1354 1416 5031 7801

Can you please advise on how my measure formula should be? I am not able to get the expected result. Thank you very much for your help/guidance.

This is not enough information to give you a clear answer.  Are no-show, rescheduled, show, Total measures or data in a column?

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Helper I

Thank you for your help.  Please think of this as a 3 column table: appt_id, appt_date, appt_status. And appt_status has entries like show, no-show, rescheduled. The count of those statuses are what I provided in the table for each month of the year.

So, you have something like:

apptid |   appt_date    |  appt_status

101        Jan-1-2017       Show

102        Jan-2-2017       NoShow

103        Jan-3-2017       Rescheduled

....

....

What I want to do is calculate the % of each appt_status by the month. Does that help?

well you really should get a calendar table first and join this data table to the calendar table using the date columns to create the join.

http://exceleratorbi.com.au/power-pivot-calendar-tables/

You need a month number/ID in the calendar table.  I would sugggest one like MMM-YY as being easiest.  Then you set up a visual with the above column from teh calendar table, and you can write measures like this

Total Appts = distinctcount(tableName[apptID])

% of Status = divide([Total Appts],calculate([total appts],all(TableName[Appt_Status])))

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Helper I

I am trying to do this same thing (but not by date), I tried using the formula as it was written but the percentages kept coming out to 100%.  I can't seem to get it to divide by the total.  Here is an example of the raw data:

Employment Status            Person ID

FT                                1001

PT                                1002

FT                                1003

None                           1004

PT                                1005

PT                                1006

FT                                1007

None                           1008

PT                                1009

PT                                1010

I took this data and aggregated it to this:

Employment Status          Count(Person ID)

FT                                   3

PT                                   5

None                                2

Total                                 10

I can't figure out how to divide by the total to get the percent versus the count.  I tried this but kept getting 1 for each employment type.  This is what I did, can you tell me what is wrong?

Total People = distinctcount(tableName[PersonID])

% of Status = divide([Total People],calculate([total people],all(TableName[Person ID])))

I also tried this and got the same result:

Total People = distinctcount(tableName[PersonID])

% of Status = divide([Total People],calculate([total people],all(TableName[Employment status])))

the first set of formulas will work for the first table you have provided.  The second set will work for the second table.

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Helper I

So I used the first set of measures, but this is what I get all 1's.  what am I doing wrong that it isn't working for me.

I Can't help you unless you provide more detail. Have a read of this and then post back all the relevant information

https://exceleratorbi.com.au/get-help-power-bi/

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Helper I

Hi Matt, I had posted a screen shot of the data and prior to that an example of what I was trying to do.  It is all on this string.

I was able to finally figure it out with a co worker.  I did not even need to write any new measures. I only had to go to the values section and in the drop down select the % of total and it did it for me.

Appreciate you trying,

Anne

Helper I

Anybody who can help with my request on calculating % by grand total? Thank you very much.

Helper I

This formula really helped me too, thanks

New Member

Thanks Matt

Announcements

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!