Forum Discussion

harirao's avatar
harirao
Icon for Post Prodigy rankPost Prodigy
1 year ago
Solved

Issue with DAX Calculations – Incorrect Results in Power BI

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,

 

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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

  • harirao's avatar
    harirao
    Icon for Post Prodigy rankPost Prodigy
    1 year ago

    Hi Anonymous 

    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,

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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