Calculate Values as Percentage of Month total

[Anonymous]

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

. 

[amitchandak]

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

[AllisonKennedy]

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

[Anonymous]

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. 

[amitchandak]

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

[StefanoGrimaldi]

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, 

[Anonymous]

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%