Variable changes the outcome

[Kopek]

Hi!

I have built the following measure, and wanted to otimize it with variable, but ufortunately it does not work as expected.
When variable is used i am missing totals from the table, and the outcomes is different, looks like it misses the summarize step, can you help me in finding a mistake, please ?

Also - if we can add additional filter for Calendar[Today]= true would be great, i am having so many isuuses with meeting this requirement as it is true/false column and i ma live connected and do not have access to adding conditional column, changing model etc.

Measure =

CALCULATE(

    -1 * SUMX(

        FILTER(

            SUMMARIZE( Query, Query[Flag],

                Products[ID],

                "Total",

                IF(

                    [Current]

                        - [Demand] * 7

                        + [Average] * 7 >= 0,

                    0,

                    (

                        [Current]

                            - [Demand] * 7

                            + [Average] * 7

                    ) * AVERAGE(Table[Price])

                )

            ),

            [Total] <> BLANK()

        ),

        [Total]

    )

with variable:

MeasureVar =

VarCalc =  [Current]- [Demand] * + [Average] * 7

Return

CALCULATE(

    -1 * SUMX(

        FILTER(

            SUMMARIZE( Query, Query[Flag],

                Products[ID],

                "Total",

                IF(

                   Calc >= 0,

                    0,

                    ( Calc

                    ) * AVERAGE(Table[Price])

                )

            ),

            [Total] <> BLANK()

        ),

        [Total]

    )

[Kopek]

Hi @DataNinja777 , thanks for trying to help, but unfortunately your solution makes the same error as mine -no total in the table and different outcomes per ID than original measure.

[v-rzhou-msft]

Hi @Kopek ,

I think you can try ALL() or ALLSELECTED() function to check whether it could show Total.

Your issue is based on your data model, I suggest you to share a sample file with us and show us a screenshot with the result you want.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gmsamborn]

Hi @Kopek 

Would it be possible to replicate the problem using sample data in a pbix that you would be able to share?

Also required would be the definitions for [Current], [Demand], and [Average] (and any other required measures) as well as their underlying tables.

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[DataNinja777]

Hi @Kopek ,

The issue with your optimized measure using variables is likely related to how Power BI handles evaluation contexts and the SUMMARIZE function. When using variables inside SUMMARIZE, their context can become detached from the rows you’re trying to calculate totals for. Shown below is a solution that optimizes the logic while addressing the missing totals and adding the additional filter you mentioned.

MeasureVar = 
VAR Calc = [Current] - [Demand] * 7 + [Average] * 7
VAR PriceAvg = AVERAGE(Table[Price])

RETURN
CALCULATE(
    -1 * SUMX(
        FILTER(
            SUMMARIZE(
                Query, 
                Query[Flag], 
                Products[ID], 
                "Total", 
                IF(
                    Calc >= 0, 
                    0, 
                    Calc * PriceAvg
                )
            ), 
            [Total] <> BLANK()
        ), 
        [Total]
    ),
    Calendar[Today] = TRUE
)

By calculating variables outside SUMMARIZE, we ensure the correct context is passed. Additionally, SUMMARIZE now only focuses on grouping and returning the necessary values, while CALCULATE manages the filtering logic effectively.

Best regards,