Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello all!
I'm trying to use the "Line and Stacked Column Chart" to show two values related to service delivery:
1) Number of households provided service (catogorised by periods of time)
2) Average number of days per month for service delivery.
I would like to show both series by month (X axis = month)
Here's the problem: I want to employ a calculated measure to show the column values as a percentage of the monthly total rather than absolute values. Essnetially, I need the attached chart, just with the columns as a % of monthly total rather than an absolute value
.
@Anonymous ,
, Try measures like
% of month = divide([measure], calculate([measure],allexcept(Table, Table[Month]))
or
% of month = divide([measure], calculate([measure],filter(allselected(Table), Table[Month] =max(Table[Month]))))
@Anonymous You can use DIVIDE for percentage and CALCULATE to change the filter context and give you the month total:
Measure = DIVIDE( [CountServices] , CALCULATE( [CountServices], ALL(Table[day_group])) )
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Hi Allison,
I'm not sure I understand the filtering here using the CALCULATE variable. Apologies, I'm quite a basic user.
If I follow correctly, your "[CountServices]" is the sum of households assisted?
If I create a calculated measure for sum of households assisted, "[sum_hh]", then place it into your formula, it doesn't give the desired outcome. It seems like I need to use CALCULATE to filter the table so that I get a numerator that is sum of households filtered by [day_group] and [month] and a denominator that is filtered just by [month] but I don't know how.
@Anonymous , Try measures like
% of month = divide([measure], calculate([measure],allexcept(Table, Table[Month]))
or
% of month = divide([measure], calculate([measure],filter(allselected(Table), Table[Month] =max(Table[Month]))))
you can change the field setting from the column value assigned to show as % of the grand total:
that will make the column values to show as a % of the total column value,
Proud to be a Super User!
Hi Stefano,
Unfortunately that just gives me the percentage of the total, whereas I want the percentage of each month. So, each month would be out of 100%
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
23 | |
12 | |
8 |
User | Count |
---|---|
76 | |
51 | |
45 | |
16 | |
12 |