Forum Discussion

LL_Yoor's avatar
LL_Yoor
Frequent Visitor
2 years ago
Solved

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...
  • bhanu_gautam's avatar
    bhanu_gautam
    2 years ago

    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]

bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
2 years ago

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]