Forum Discussion

MacarenaGB's avatar
MacarenaGB
Helper II
5 years ago
Solved

Filtering and calculation issues

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 Hol...
Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    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:

     

MFelix's avatar
MFelix
Super User
5 years ago

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: