Filtering and calculation issues

[MacarenaGB]

Hi everyone

I have a table with the effort tracking in man-days of the employees related to the date and I am trying to join this info with their cost, but I need to associate the time of the Holidays to the other projects prorrated to the man-days.

In excel should be like this (example):

Here you have the initial result in power BI and the result applying the measure detailed below

As you can see the result is not correct

Can anyone help me?

Thanks

[MFelix · Accepted solution]

Hi @MacarenaGB ,

Believe the best option would be to have the holidays out of your table but having that value on your table you should do your measure similar to this one:

Adjusted Cost = 
var Holidays_unit_value =
DIVIDE (
    CALCULATE (
        SUM ( 'Table'[Cost] ),
        FILTER ( ALL ( 'Table'[Project Name] ), 'Table'[Project Name] = "Holidays" )
    ),
     (
        CALCULATE ( SUM ( 'Table'[Cost] ), ALL ( 'Table'[Project Name] ) )
            - CALCULATE (
                SUM ( 'Table'[Cost] ),
                FILTER ( ALL ( 'Table'[Project Name] ), 'Table'[Project Name] = "Holidays" )
            )
    )
)

var SalaryCost = DIVIDE(SUM('Table'[Cost]),SUM('Table'[Man-Days]))
Return
COALESCE (
    IF (
        ISINSCOPE ( 'Table'[Project Name] ),
        IF (
            SELECTEDVALUE ( 'Table'[Project Name] ) = "Holidays",
            BLANK (),
            SUM ( 'Table'[Man-Days] ) * SalaryCost
                + (
                    SUM ( 'Table'[Man-Days] ) * Holidays_unit_value * SalaryCost
                )
        ),
        SUM ( 'Table'[Cost] )
    ),
    0
)

See result below: