Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to 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.
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.
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?
I would recommend you have a read of this and provide more information
http://exceleratorbi.com.au/get-help-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])))
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.
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/
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
89 | |
88 | |
82 | |
64 | |
49 |
User | Count |
---|---|
125 | |
111 | |
88 | |
69 | |
66 |