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
RMDNA's avatar
RMDNA
Solution Sage
8 years ago

ek2112,

 

Can you provide a visual of your expected end result? I know how to do this, I just need to see what you want.

ek2112's avatar
ek2112
Advocate II
8 years ago

RMDNA

 

Thanks for looking into this. Here is how I envision the expected result (I mocked this up in Excel):

 

 

 

 

 

  • RMDNA's avatar
    RMDNA
    Solution Sage
    8 years ago

    Very helpful - if that's fabricated or anonymous data, it'd be useful if you could upload it to Dropbox/OneDrive/etc. and share the xlsx directly.

      • 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