Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
babarbashir
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. 

age_groups_date.jpg

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

 

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



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

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

 

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



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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. 

 

monthno-showrescheduledshowTotal
Jan-161675166454968835
Feb-161612160057168928
Mar-161525170056988923
Apr-161354141650317801

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?


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


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



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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.

Doc1.jpg

 

 

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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

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

This formula really helped me too, thanks

Thanks Matt

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.