Issue with DAX Calculations – Incorrect Results in Power BI

[harirao]

Hi All,

I have created two DAX measures, but I’m not getting the expected results in Power BI. Below are the details:

1) Subtraction: [Order RSD] - [Qtr Baseline Fcst]

Qtr Baseline Fcst =

LOOKUPVALUE(

    Base[Total],             -- Column to return

    Base[CONCATENATE],       -- Column to search in Base

    '09_Data'[CONCATENATE]   -- Value to search for from 09_Data

)
 & 

Order RSD =

CALCULATE (

    [5_Q3-24],  -- This references your measure

    '09_Data'[Measure] = "Total Deal Fcst Published Order Actuals (RSD)",

    '09_Data'[Type] = "UN"

)

2) Division: [Order RSD] / [Qtr Baseline Fcst]

Attn% = --[Orders RSD] / SUM('09_Data'[Qtr Baseline Fcst])

SUMX(

    '09_Data',

    VAR cleanConcat = TRIM(UPPER('09_Data'[CONCATENATE]))

    VAR baseline = LOOKUPVALUE(

        Base[Total],

        Base[CleanCONCATENATE],  -- ✅ Now this is a real column

        cleanConcat

    )

    RETURN

    IF(

        NOT ISBLANK(baseline),

        baseline / '09_Data'[Qtr Baseline Fcst],

        BLANK()

    )

)

When I verify the same logic in Excel, the values are coming out correctly. However, in Power BI, the results are incorrect.

Could you please help me understand what might be going wrong and assist me in resolving this issue?

Thank you,

[harirao]

Hi @v-vpabbu 

I was able to find a solution for the subtraction logic [Order RSD] - [Qtr Baseline Fcst] using the below DAX:

TO GO =

SUMX(

    '09_Data',

    VAR baseline = LOOKUPVALUE(

        Base[Total],

        Base[CONCATENATE],

        '09_Data'[CONCATENATE]

    )

    VAR orderRSD =

        CALCULATE(

            [Order RSD],

            FILTER(

                ALL('09_Data'),

                '09_Data'[CONCATENATE] = EARLIER('09_Data'[CONCATENATE])

            )

        )

    RETURN

    orderRSD - baseline

)

However, when I apply a similar approach for division, i.e., [Order RSD] / [Qtr Baseline Fcst], it doesn't return the expected results.

Do you have any suggestions on how to resolve this issue?

Thanks in advance for your help.

Best regards,

[v-vpabbu · Accepted solution]

Hi @harirao,

For division, [Order RSD] must be recalculated per row using CALCULATE + FILTER, and baseline fetched using LOOKUPVALUE, so both values align per row.

Regards,

Vinay Pabbu

[v-vpabbu]

Hi @harirao,

Thank you for reaching out to Microsoft Fabric Community Forum.

Excel works with static rows, whereas Power BI measures operate within a dynamic filter context.

When you use a calculated column, it evaluates row by row, which aligns more closely with Excel behavior. However, when you use a measure like LOOKUPVALUE, it doesn't iterate row by row—it calculates based on the current filter context, which may not give you the expected results.

If you need to replicate row-by-row logic within a measure, consider using SUMX or another iterator function to explicitly control the row-level behavior.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Regards,
Vinay Pabbu