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,

 

In order to keep the formula short, I would suggest you create a new measure which will invoke the old one. Please give it a try.

New =
SUMX (
    SUMMARIZE (
        'Work Details',
        'Work Details'[Name],
        'Work Details'[Employee Type],
        'Work Details'[Project Name],
        'Work Details'[Work date],
        "DEC", [Daily Emp Cost New]
    ),
    [DEC]
)

Dynamic_calculation_based_on_slicer_selection3

 

Best Regards,

Dale

ek2112's avatar
ek2112
Advocate II
8 years ago

Hello v-jiascu-msft

 

Thanks again, It worked if there are few rows in 'Work Details' table, but when I added more rows for another employee, the behaviour is weird - the measure is coming up as empty. 

 

 

Here is the pbix.

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

    Hi ek2112,

     

    Please comfirm if the blue part (<=) is good. That's the cause.

    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, // Just change from < to <=
            IF (
                MIN ( [Employee Type] ) = "Contractor",
                MIN ( [Work Time] ) * rate_selected,
                IF ( MIN ( 'Work Details'[Index] ) = minIndex, 8 * rate_selected, 0 )
            )
        )
    )

    Best Regards,

    Dale

  • ek2112's avatar
    ek2112
    Advocate II
    8 years ago

    v-jiascu-msft I tried again after changing the formula, but the "Contractor" calculation is not coming right for some - for example,  as per this screenshot, for AA, it should be 31.50 * 60 = 1890, but coming up as 450. Rest of the three came up fine. Here is the pbix. Thanks again.

  • ek2112's avatar
    ek2112
    Advocate II
    8 years ago

    v-jiascu-msft after quite a bit of effort, I tweaked the formula a bit and got it working. Thanks for your help. 

     

    Just one final question - is there a way to get the actual costs based on different rates for different time periods? E.g. For one resource, if the resource has $30/hr for Jan, cost = [work time] * 30, else cost = [work time] * rate_selected. I tried DATEDIFF but could not quite get it. Thanks.

     

    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 (MIN ( [Employee Type] ) = "Full-time", IF ( MIN ( 'Work Details'[Index] ) = minIndex, IF (SELECTEDVALUE ( 'Work Details'[Weekend] ) = FALSE (), 8* rate_selected, IF(SELECTEDVALUE ( 'Work Details'[Weekend] ) = TRUE(), 0, MIN ([Work Time]) * rate_selected)),IF(MIN ( [Employee Type] ) = "Contractor", MIN ( [Work Time] ) * rate_selected,0)),MIN ( [Work Time] ) * rate_selected)
  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi ek2112,

     

    So glad you solve it. Maybe you can set another VAR to get the $30/hr then you can apply it in the formula. That will depend on where we can get the $30/hr.

     

    Best Regards,

    Dale

  • ek2112's avatar
    ek2112
    Advocate II
    8 years ago

    v-jiascu-msft thanks will try this out. I am basically hardcoding this within the formula? If you can give me something by changing the formula, that would be awesome. Thanks.