Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ek2112
Advocate II
Advocate II

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 - Rate 2, Rate 3 and Rate 4 - with different rates for each employee as shown below.

 

Is there a way I can display a slicer with just 'Rate', 'Rate 2' , 'Rate 3' and 'Rate 4' in the drop down and trigger calculations in related columns of the 'Work Details' table (like 'Daily Emp Cost' column) that currently points to 'Rate' column of the 'Rate' table, as a What if scenario? I tried with HASONEVALUE function, but could not wrap my head around it. Thanks for your help.

 

Here is the pbix file.

 

Rate table:

 

image.png

Work details table:

image.png

 

 

 

18 REPLIES 18
RMDNA
Solution Sage
Solution Sage

@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.

@RMDNA

 

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

image.png

 

 

 

 

 

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.

@RMDNAhere is the link to the data sheet.

 

Note that I have shared the pbix in my earlier post. 

 

 

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@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 isimage.png for Contractor employee type. I have illustrated it for 1/04/18 entries in the following screenshot.

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@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 

@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. image.png

Hi @ek2112,

 

There aren't any [work time] or "daily hours" existed in monthly level.

1. How can we handle it? Sum the daily level values? 

2. How about other levels? Such as, Year.

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-jiascu-msft Thanks for your response.

 

1. Yes, I am trying to sum the [work time] at monthly/yearly level.

2. Same as 1. above.

 

Can the application not take the daily level [work time] hours and do summation?

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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. 

 

image.png

 

Here is the pbix.

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@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.image.png

@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)

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.