Power Bi condition based formula

[Anonymous]

Hi, if I have data as below, does anyone know how I can embed the following forumla:

(0.1+sum(INITIAL_BOOKED_SHIFTS)- sum(ACTUAL_SHIFTS))/(0.1+sum(ACTUAL_SHIFTS))*100 

in a new column, but based on each unique "APPLICANT_COMPANY" and "WORK_AREA. 

[mahoneypat · Accepted solution]

Please try this column expression

NewColumn =
VAR vActuals =
    CALCULATE (
        SUM ( Overbooking[ACTUAL_SHIFTS] ),
        ALLEXCEPT (
            Overbooking,
            Overbooking[Applicant_Company],
            Overbooking[Work_Area]
        )
    )
VAR vBooked =
    CALCULATE (
        SUM ( Overbooking[Initial_Booked_Shifts] ),
        ALLEXCEPT (
            Overbooking,
            Overbooking[Applicant_Company],
            Overbooking[Work_Area]
        )
    )
VAR vResult =
    DIVIDE (
        0.1 + vBooked - vActuals,
        0.1 + vActuals
    ) * 100
RETURN
    vResult

Pat

[Icey · Accepted solution]

Hi @Anonymous ,

You can also try to create a calculated column like this:

NewColumn =
VAR vActuals =
    CALCULATE (
        SUM ( Overbooking[ACTUAL_SHIFTS] ),
        FILTER (
            Overbooking,
            Overbooking[Applicant_Company] = EARLIER ( Overbooking[Applicant_Company] )
                && Overbooking[Work_Area] = EARLIER ( Overbooking[Work_Area] )
        )
    )
VAR vBooked =
    CALCULATE (
        SUM ( Overbooking[Initial_Booked_Shifts] ),
        FILTER (
            Overbooking,
            Overbooking[Applicant_Company] = EARLIER ( Overbooking[Applicant_Company] )
                && Overbooking[Work_Area] = EARLIER ( Overbooking[Work_Area] )
        )
    )
VAR vResult =
    DIVIDE ( 0.1 + vBooked - vActuals, 0.1 + vActuals ) * 100
RETURN
    vResult

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[mahoneypat · Accepted solution]

Please try this column expression

NewColumn =
VAR vActuals =
    CALCULATE (
        SUM ( Overbooking[ACTUAL_SHIFTS] ),
        ALLEXCEPT (
            Overbooking,
            Overbooking[Applicant_Company],
            Overbooking[Work_Area]
        )
    )
VAR vBooked =
    CALCULATE (
        SUM ( Overbooking[Initial_Booked_Shifts] ),
        ALLEXCEPT (
            Overbooking,
            Overbooking[Applicant_Company],
            Overbooking[Work_Area]
        )
    )
VAR vResult =
    DIVIDE (
        0.1 + vBooked - vActuals,
        0.1 + vActuals
    ) * 100
RETURN
    vResult

Pat

[themistoklis]

@Anonymous 

Create a new measure and not column and add the following formula.

Measure = (0.1+SUM(OVERBOOKING[INITIAL_BOOKED_SHIFTS])- SUM(OVERBOOKING[ACTUAL_SHIFTS])) / (0.1+SUM(OVERBOOKING[ACTUAL_SHIFTS]))*100

Then Create a table and add the fields "APPLICANT_COMPANY", "WORK_AREA and the measure above