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

 

 

 

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:

     

1 Reply

  • 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: