Calculated Column returning same values

[mp390988]

Hi, 

I have the below formula in my calculated column:

MTDRev = CALCULATE(

    SUM(REVENUE[GBPRevenue]),

    TREATAS(VALUES('Top N Clients'[TDMonth]), REVENUE[TDMonth]),

    TREATAS(VALUES('Top N Clients'[DealerGroup]), REVENUE[DealerGroup]),

    TREATAS(VALUES('Top N Clients'[DealerID]), REVENUE[DealerID]),

    TREATAS(VALUES('Top N Clients'[ClientID]), REVENUE[ClientID])

)

I would have expected to see different values in each row from that formula but instead it is repeating the same value 68,636,210.75

I thought the CALCULATE will create context transition so that the filters from the current row would get passed onto the VALUES function so in the end my VALUES function inside the TREATAS will be a 1 column and 1 row table with the value being the current row's values.

Am I missing something here?

Thanks,

[v-prasare]

Hi @mp390988,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[v-prasare]

Hi @mp390988,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[mp390988]

Hi @Omkar_1712 , @johnt75 and @OwenAuger ,

Thank you to each and every one of you for your replies.

I’m honestly a bit baffled, as I had never come across the concept of evaluation order before.

Up until now, whenever I’ve used calculated columns, I haven’t run into this issue. In hindsight, I was working under the assumption described in my original post — i.e. calculated column = row context, and CALCULATE = context transition.

It seems there’s another important factor to consider now - evaluation order, which I’ll now need to keep in mind going forward.

I don’t quite understand it yet, so I’ll need some time to read up on it and get my head around it.

Thank You,

M

[OwenAuger]

Hi @mp390988 

The filter arguments passed to CALCULATE are evaluated in the context from which CALCULATE was called, which, in this case, is in the row context of the 'Top N Clients' table with an empty filter context. In other words, before context transition.

However they are then applied after context transition.

This SQLBI article has a good explanation.

To fix your expression, you could write something like any of these that retrieve the values from the row context where CALCULATE is called:

MTDRev =
CALCULATE (
    SUM ( REVENUE[GBPRevenue] ),
    TREATAS ( { 'Top N Clients'[TDMonth] }, REVENUE[TDMonth] ),
    TREATAS ( { 'Top N Clients'[DealerGroup] }, REVENUE[DealerGroup] ),
    TREATAS ( { 'Top N Clients'[DealerID] }, REVENUE[DealerID] ),
    TREATAS ( { 'Top N Clients'[ClientID] }, REVENUE[ClientID] )
)

or

MTDRev =
CALCULATE (
    SUM ( REVENUE[GBPRevenue] ),
    TREATAS (
        {
            ( 'Top N Clients'[TDMonth], 'Top N Clients'[DealerGroup], 'Top N Clients'[DealerID], 'Top N Clients'[ClientID] )
        },
        REVENUE[TDMonth],
        REVENUE[DealerGroup],
        REVENUE[DealerID],
        REVENUE[ClientID]
    )
)

[johnt75]

When you are calling the VALUES function you have a row context but that is not turned into a filter context because there is no context transition - the CALCULATE performs context transition only for the SUM.

You can use the values from the current directly, because you have a row context, so your measure would become

MTDRev =
CALCULATE (
    SUM ( REVENUE[GBPRevenue] ),
    TREATAS (
        {
            ( 'Top N Clients'[TDMonth], 'Top N Clients'[DealerGroup], 'Top N Clients'[DealerID], 'Top N Clients'[ClientID] )
        },
        REVENUE[TDMonth],
        REVENUE[DealerGroup],
        REVENUE[DealerID],
        REVENUE[ClientID]
    )
)

[Omkar_1712]

Hello @mp390988,

The fact that every row returns the same value suggests that the filter context being generated by your TREATAS() statements is not changing per row.

As a quick test, try replacing one of the VALUES() calls with a direct reference to the current row value and see if the result changes. For example, store the current row values in variables and use those variables in your filter logic.

You may also want to create a simple measure first to validate that the combination of TDMonth, DealerGroup, DealerID, and ClientID is uniquely filtering the REVENUE table as expected.

If the goal is to calculate revenue dynamically based on report filters, a measure may be a better fit than a calculated column.

Can you confirm:

• Is there a relationship between Top N Clients and REVENUE?
• Does the combination of TDMonth, DealerGroup, DealerID, and ClientID uniquely identify a row?

Those details would help narrow down the root cause.

Best regards,
Omkar Shinde
Microsoft Fabric Enthusiast | Power BI Consultant

💡 If you found this response helpful, please consider giving it a Kudos.
✅ If this resolves your question, please mark it as the Accepted Solution to help others in the community.