percent of grand total

[babarbashir]

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. 

[MattAllington · 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

=divide([your current measure],calculate([your current measure],all(table[age group])))

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

[Sid10]

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. 

[MattAllington]

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

I would recommend you have a read of this and provide more information

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

[Sid10]

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? 

[MattAllington]

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

[annerogers]

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

[MattAllington]

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

[annerogers]

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.

[MattAllington]

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/

[annerogers]

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

[Sid10]

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

[ianmonat]

This formula really helped me too, thanks

[babarbashir]

Thanks Matt