Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Power BI If Statement Totals error

I hope you can help me with some DAX query. Basically I get error in the totals or totals dont show up at all for the measueres I am trying to create.

The query is simple, if months cover is less than 4 then (Current Stock/ months cover) * 4 otherwise zero.

 

I have created two DAX queries which is correct at the line level but their total does not show correct result. Am I doing anything wrong in the query ? 

 

Target Qty = IF(Stock_CurrentMonth1[Months Cover SKU]<4,DIVIDE(SUM(Stock_CurrentMonth1[Total Stock Qty]),Stock_CurrentMonth1[Months Cover SKU])*4,0)
 
Target Value = IF(Stock_CurrentMonth1[Months Cover SKU]<4,DIVIDE(SUM(Stock_CurrentMonth1[Total Stock Qty]),Stock_CurrentMonth1[Months Cover SKU],0)*4)*SUM(Range_Product[NET_PRICE])
 
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    6 years ago

    Instead of doin the SUM in the measure you should write a measure just to sum the stock qty.

    Stock Qty = SUM ( Stock_CurrentMonth1[Total Stock Qty] )
    Net Price = SUM ( Range_Product[NET_PRICE] )

    Then you use the measures in your SUMX

    Target Qty =
SUMX (
    VALUES ( Stock_CurrentMonth1[SKU] ),
    IF (
        Stock_CurrentMonth1[Months Cover SKU] < 4,
        DIVIDE (
            [Stock Qty],
            Stock_CurrentMonth1[Months Cover SKU],
            0
        ) * 4
    )
        * [Net Price]
)
    Target Qty =
SUMX (
    VALUES ( Stock_CurrentMonth1[SKU] ),
    IF (
        Stock_CurrentMonth1[Months Cover SKU] < 4,
        DIVIDE (
            [Stock Qty],
            Stock_CurrentMonth1[Months Cover SKU]
        ) * 4,
        0
    )
)

7 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    Hi Anonymous ,

    By my tests, I cannot reproduce your output based on your formula.

    If it is convenient, could you share the data sample as table format and your desired output so that we could have a test on it?

    Best  Regards,

    Cherry

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi v-piga-msft ,

       

      Many thanks for looking into this issue. your output seems fine, I believe its just decimal rounding issue. I am able to get correct results at line level using the formulae, however the problem occures when displaying the totals. The total is either incorrect as you can see the Target Value is not the correct total. The total should be $402,356 , but Power BI displays $758,166. 

      SKU Sum of Average Sales Sum of Total Stock QtySum of Avg Sales ValueSum of Stock Value Months Cover SKU Target QtyTarget Value
      Product 38     150.50345$74,755$171,366        2.292602$299,021
      Product 107     103.33347$25,834$86,751        3.358413$103,335
      • jdbuchanan71's avatar
        jdbuchanan71
        Icon for Super User rankSuper User
        6 years ago

        Hello Anonymous 

        Your sample didn't show the total row but unexpected totals are always a product of filter context.  My guess is changing your measures to use SUMX will fix problem.

         

        Target Qty =
SUMX (
    VALUES ( Stock_CurrentMonth1[SKU] ),
    IF (
        Stock_CurrentMonth1[Months Cover SKU] < 4,
        DIVIDE (
            SUM ( Stock_CurrentMonth1[Total Stock Qty] ),
            Stock_CurrentMonth1[Months Cover SKU],
            0
        ) * 4
    )
        * SUM ( Range_Product[NET_PRICE] )
)
        Target Qty =
SUMX (
    VALUES ( Stock_CurrentMonth1[SKU] ),
    IF (
        Stock_CurrentMonth1[Months Cover SKU] < 4,
        DIVIDE (
            SUM ( Stock_CurrentMonth1[Total Stock Qty] ),
            Stock_CurrentMonth1[Months Cover SKU]
        ) * 4,
        0
    )
)