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 ( ...
Need Help
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.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Thanks Anonymous ,

       

      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!!