Forum Discussion

ek2112's avatar
ek2112
Advocate II
8 years ago

Dynamic calculation based on slicer selection

I have two tables, 'Work Details' and 'Rate' and linked by 'Name' column. Each employee has their own hourly rates in the 'Rate' table's 'Rate' column.   I have additional columns in the Rate table...
Need Help
v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

Hi ek2112,

 

The calcualted column can't be changed by the slicer. I would suggest you create a measure instead. Please also refer to the attached demo.

1. Pivot the Rate table, 

2. Create a measure.

Daily Emp Cost New =
VAR dailyhours =
    CALCULATE (
        SUM ( 'Work Details'[Work Time] ),
        ALLEXCEPT ( 'Work Details', 'Work Details'[Name], 'Work Details'[Work date] )
    )
VAR rate_selected =
    IF (
        HASONEVALUE ( Rate[Rates] ),
        LOOKUPVALUE (
            Rate[Value],
            Rate[Name], MIN ( 'Work Details'[Name] ),
            Rate[Rates], MIN ( Rate[Rates] )
        ),
        LOOKUPVALUE (
            Rate[Value],
            Rate[Name], MIN ( 'Work Details'[Name] ),
            Rate[Rates], "Rate"
        )
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Work Details'[Weekend] ) = FALSE (),
        IF (
            dailyhours < 8,
            IF (
                MIN ( [Employee Type] ) = "Contractor",
                MIN ( [Work Time] ) * rate_selected,
                8 * rate_selected
            )
        )
    )

Dynamic_calculation_based_on_slicer_selection

Best Regards,

Dale

ek2112's avatar
ek2112
Advocate II
8 years ago

v-jiascu-msft great thank you. The measure currently has repeated entries of for Full-time employees. Is it possible to get just one entry of Daily empocost per day per full-time employee and have the rest as zero entries in the Daily Emp Cost New measure column? It is fine as it is for Contractor employee type. I have illustrated it for 1/04/18 entries in the following screenshot.

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi ek2112,

     

    Try this new measure please.

    Daily Emp Cost New =
VAR minIndex =
    CALCULATE (
        MIN ( 'Work Details'[Index] ),
        ALLEXCEPT ( 'Work Details', 'Work Details'[Name], 'Work Details'[Work date] ),
        'Work Details'[Employee Type] = "Full-time"
    )
VAR dailyhours =
    CALCULATE (
        SUM ( 'Work Details'[Work Time] ),
        ALLEXCEPT ( 'Work Details', 'Work Details'[Name], 'Work Details'[Work date] )
    )
VAR rate_selected =
    IF (
        HASONEVALUE ( Rate[Rates] ),
        LOOKUPVALUE (
            Rate[Value],
            Rate[Name], MIN ( 'Work Details'[Name] ),
            Rate[Rates], MIN ( Rate[Rates] )
        ),
        LOOKUPVALUE (
            Rate[Value],
            Rate[Name], MIN ( 'Work Details'[Name] ),
            Rate[Rates], "Rate"
        )
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Work Details'[Weekend] ) = FALSE (),
        IF (
            dailyhours < 8,
            IF (
                MIN ( [Employee Type] ) = "Contractor",
                MIN ( [Work Time] ) * rate_selected,
                IF ( MIN ( 'Work Details'[Index] ) = minIndex, 8 * rate_selected, 0 )
            )
        )
    )

    Dynamic_calculation_based_on_slicer_selection2

     

    Best Regards,

    Dale

    • ek2112's avatar
      ek2112
      Advocate II
      8 years ago

      v-jiascu-msft that worked for day level. If I try to sum at the month/quarter/year level,  this returns empty especially whe there is date hierarchy 

      • ek2112's avatar
        ek2112
        Advocate II
        8 years ago

        v-jiascu-msft  just noticed that the measure is not working if summed at the monthly level, ie if I use the date hierarchy for work date.