Incorrect SUMX total (again)

[PBI_hlpr1]

Hi all,

I am using the following measure to calculate Price impact of sales variation:

∆Price =
SUMX (
    SUMMARIZE ( Fact_Sales, Fact_Sales[Cli_ID], Fact_Sales[Prod_ID] ),  
        IF (
            AND (
                [Qty_Comp1] * [Qty_Comp2] <> 0,
                AND (
                    LEFT ( MAX ( Fact_Sales[Prod_ID] ), 1 ) <> "_",
                    LEFT ( MAX ( Fact_Sales[Cli_ID] ), 1 ) <> "_"
             )
        ),
        ( [PU_Comp2] - [PU_Comp1] ) * [Qty_Comp2],  -- Pricing impact calcuation formula
        BLANK ()
    )
)

When the measure is used on a tale with Client and product in the rows, it works perfectly. Unfortunately the total comes wrong.

Are you able to support? I know the problem of wrong SUMX totals has been treated already but my use case looks different.

Thanks,

C.

[v-rongtiep-msft · Accepted solution]

Hi @PBI_hlpr1 ,

Please refer to  Solved: Measure Total Incorrect - Microsoft Fabric Community .

Solved: Wrong Totals with SUMX Measure - Microsoft Fabric Community

Solved: Wrong totals on SUMX column - Microsoft Fabric Community

Best Regards
Community Support Team _ Rongtie

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

[PBI_hlpr1]

Thanks @v-rongtiep-msft , I made it working even if is still slower than the two "connected measures".
Thanks again for your support!

Regards,

C.

[v-rongtiep-msft]

Hi @PBI_hlpr1 ,

Please create another measure based on [Price].

_Price_M =
VAR _b =
    SUMMARIZE ( fact_sale, fact_sale[date], "aaa", fact_sale[Price] )
RETURN
    IF ( HASONEVALUE ( fact_sale[date] ), fact_sale[Price], SUMX ( _b, [aaa] ) )
//Change the fact_sale[date] to the unique value in the table

 Fixing Incorrect Totals Using DAX Measures In Power BI | Enterprise DNA

Solved: Re: conditional measure total is wrong in power bi... - Microsoft Fabric Community

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[PBI_hlpr1]

Thanks @v-rongtiep-msft !! Unfortunately your sample does not reflect my use case as I do not have a column Price in my fact_sales table. All is based on measures calculating variances. So I should modify last line of your code as follows:

RETURN

      IF ( HASONEVALUE ( fact_sales[key] ), [aaa] , SUMX ( _b, [aaa] ) )

but the first reference to [aaa] clearly does not work. How can I return the value present in the column [aaa] of the virtual table?

[v-rongtiep-msft]

Hi @PBI_hlpr1 ,

The measure I created is based on this price measure of yours.The order of precedence is shown below.

∆Price =
SUMX (
    SUMMARIZE ( Fact_Sales, Fact_Sales[Cli_ID], Fact_Sales[Prod_ID] ),  
        IF (
            AND (
                [Qty_Comp1] * [Qty_Comp2] <> 0,
                AND (
                    LEFT ( MAX ( Fact_Sales[Prod_ID] ), 1 ) <> "_",
                    LEFT ( MAX ( Fact_Sales[Cli_ID] ), 1 ) <> "_"
             )
        ),
        ( [PU_Comp2] - [PU_Comp1] ) * [Qty_Comp2],  -- Pricing impact calcuation formula
        BLANK ()
    )
)

_Price_M =
VAR _b =
    SUMMARIZE ( fact_sale, fact_sale[date], "aaa", [∆Price] )
RETURN
    IF ( HASONEVALUE ( fact_sale[date] ), [∆Price], SUMX ( _b, [aaa] ) )
//Change the fact_sale[date] to the unique value in the table

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[PBI_hlpr1]

Thanks @v-rongtiep-msft ,

the first part of the measure (∆Price) works well. Unfortunately the second (_Price_M) is very slow. May sound strange but adding another SUMX gives much better performance:

_Price_M = SUMX( VALUES( Fact_Sales[Key]), [∆Price])

So I am done, but... Is there a way to assemble the two measures in one? I tried but I am getting weird results..

Thanks!!

[TobyNye]

Would be good to see a sample of the dataset alongside what it's outputting to get a better idea of it, the most likely fix would be to wrap your function in a calculate, I don't remember exactly why this works, but it's somthing about passing filter context to your measure. So you would replace the expression argument in your SUMX with the below:

CALCULATE(( [PU_Comp2] - [PU_Comp1] ) * [Qty_Comp2])

[PBI_hlpr1]

Thanks @TobyNye , I tried that but did not work (no changes).

What worked instead is to embed my calculation in another SUMX:

∆Price2 = SUMX (VALUES(Fact_Sales[Key]), [∆Price])

where Fact_Sales[Key] is the concatenation of Cli_ID and Prod_ID.

As said this works but looks very inefficient running SUMX twice on a pretty large Fact Table. Any guess?

[JGroothedde]

---

@TobyNye wrote:
I don't remember exactly why this works, but it's somthing about passing filter context to your measure. 

CALCULATE(( [PU_Comp2] - [PU_Comp1] ) * [Qty_Comp2])

---

Yes that's correct! By wrapping up the calculations in their own measures you prevent passing unwanted/ unexpected filter context into the SUMX calculation. So this should work.