Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Incorrect SUMX total (again)

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.

Need Help

9 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Thanks Anonymous , I made it working even if is still slower than the two "connected measures".
      Thanks again for your support!

      Regards,

      C.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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])

     

    • JGroothedde's avatar
      JGroothedde
      Resolver II
      2 years ago
      Anonymous 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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Thanks Anonymous , 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?


  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

    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.

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Thanks Anonymous !! 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?





      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Anonymous ,

        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.