Forum Discussion
Percentage difference from category average
Hi,
I have a fact table that records daily hours worked by cost centre (table name = daily_hours). There are different types of hours captured; e.g., hours worked by casual staff (casual_hours in the table below) and permanent staff (permanent_hours). There is also a column for total hours (total_hours). I have calculated a measure to get the % of total hours worked by casuals (measure_percent_casual) using the DAX formula:
measure_percent_casual = sum(daily_hours[casual_hours])/sum(daily_hours[total_hours])
The table looks like this:
| cost_centre | date | permanent_hours | casual_hours | total_hours | measure_percent_casual |
| 1 | 21/04/2023 | 22 | 6 | 28 | 21.43% |
| 1 | 22/04/2023 | 24 | 5 | 29 | 17.24% |
| 2 | 21/04/2023 | 34 | 3 | 37 | 8.11% |
| 2 | 22/04/2023 | 33 | 2 | 35 | 5.71% |
| 3 | 21/04/2023 | 19 | 0 | 19 | 0.00% |
| 3 | 22/04/2023 | 18 | 0 | 18 | 0.00% |
| 4 | 21/04/2023 | 44 | 2 | 46 | 4.35% |
| 4 | 22/04/2023 | 52 | 3 | 55 | 5.45% |
Each cost centre is nested in a division. The fact table recording cost centre details (table name = cc_map) looks like this:
| cost_centre | cc_name | cc_division |
| 1 | A | East |
| 2 | B | East |
| 3 | X | West |
| 4 | Y | West |
daily_hours is related to cc_map using the cost_centre field.
I would like to be able to calculate the percentage of casual hours (i.e., measure_percent_casual) at the division level based on the individual cost centre displayed/selected. That way I can report (for example) the percentage difference between an individual cost centre and the division average. The table below would give the sort of output I am looking for collapsing across the two days that are present in my sample data:
| cost_centre | average_measure_percent_casual | division_average | percent_difference |
| 1 | 19.33% | 13.12% | 6.21% |
| 2 | 6.91% | 13.12% | -6.21% |
| 3 | 0.00% | 2.45% | -2.45% |
| 4 | 4.90% | 2.45% | 2.45% |
The difficulty I am having is calculating the division average based on the cost centre selected. Any advice appreciated.
LL_Yoor , Create a measure to calculate the average percentage of casual hours
average_measure_percent_casual = AVERAGEX(VALUES(daily_hours[date]), [measure_percent_casual])
Then
Create a measure to calculate the division average percentage of casual hours:
division_average =
CALCULATE(
AVERAGEX(
VALUES(daily_hours[cost_centre]),
[average_measure_percent_casual]
),
ALLEXCEPT(cc_map, cc_map[cc_division])
)Last create a measure for percentage differnce
percent_difference = [average_measure_percent_casual] - [division_average]
1 Reply
LL_Yoor , Create a measure to calculate the average percentage of casual hours
average_measure_percent_casual = AVERAGEX(VALUES(daily_hours[date]), [measure_percent_casual])
Then
Create a measure to calculate the division average percentage of casual hours:
division_average =
CALCULATE(
AVERAGEX(
VALUES(daily_hours[cost_centre]),
[average_measure_percent_casual]
),
ALLEXCEPT(cc_map, cc_map[cc_division])
)Last create a measure for percentage differnce
percent_difference = [average_measure_percent_casual] - [division_average]