Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

TRICKY DAX Measure error

Hi Experts

 

See image below....Individual data points are corrcet but the avergaes total values for columns and rows are wrong

 

My Measure

Days ## =
VAR NumberofEmployee = COUNTROWS(VALUES('act_rates_activity_volume_and_cf_days'[employee_name]))
VAR NumberofWeeks = COUNTROWS(VALUES(act_rates_activity_volume_and_cf_days[week_start_date]))
VAR SumValues = MAX(act_rates_activity_volume_and_cf_days[Working Days])

VAR Result =
        SWITCH(    
                TRUE,
                HASONEVALUE(act_rates_activity_volume_and_cf_days[employee_name]) && HASONEVALUE(act_rates_activity_volume_and_cf_days[week_start_date]), SumValues,
               HASONEVALUE(act_rates_activity_volume_and_cf_days[employee_name]) && NOT(HASONEVALUE(act_rates_activity_volume_and_cf_days[week_start_date])), DIVIDE(SumValues,NumberofWeeks),
        DIVIDE(SumValues,NumberofEmployee))
Return
        Result



Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    I am not sure how your datamodel looks like, but please try something like below whether it suits your requirement.

     

    Days ## =
VAR NumberofEmployee =
    COUNTROWS ( VALUES ( 'act_rates_activity_volume_and_cf_days'[employee_name] ) )
VAR NumberofWeeks =
    COUNTROWS ( VALUES ( act_rates_activity_volume_and_cf_days[week_start_date] ) )
VAR SumValues =
    SUMX (
        VALUES ( act_rates_activity_volume_and_cf_days[week_start_date] ),
        CALCULATE ( MAX ( act_rates_activity_volume_and_cf_days[Working Days] ) )
    )
VAR Result =
    SWITCH (
        TRUE,
        HASONEVALUE ( act_rates_activity_volume_and_cf_days[employee_name] )
            && HASONEVALUE ( act_rates_activity_volume_and_cf_days[week_start_date] ), SumValues,
        HASONEVALUE ( act_rates_activity_volume_and_cf_days[employee_name] )
            && NOT ( HASONEVALUE ( act_rates_activity_volume_and_cf_days[week_start_date] ) ), DIVIDE ( SumValues, NumberofWeeks ),
        DIVIDE ( SumValues, NumberofEmployee )
    )
RETURN
    Result

2 Replies