Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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])
 
image.png
1 ACCEPTED SOLUTION

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

View solution in original post

7 REPLIES 7
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @Anonymous ,

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

Capture.PNG

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

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

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
    )
)
Anonymous
Not applicable

Hi @jdbuchanan71  thank you soo much for your assistance. Because I have to use a combination of Account and SKU to generate a unique combination, I had to use SUMX with Summarise. I got to know this after a little more investigation 🙂

 

The Query I have now is ;

 

Target Qty trial =
SUMX(
 SUMMARIZE(Stock_CurrentMonth1,Accounts[Account Name],Range_Product[SKU]),
  IF([Months Cover SKU]<4,
     DIVIDE(SUM(Stock_CurrentMonth1[Total Stock Qty]),[Months Cover SKU])*4,0
)
)
 
The next issue is in the second part. It looks like the query does not like Divide(Sum function.

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
    )
)
Anonymous
Not applicable

@jdbuchanan71 Thank you! This has solved my query. You are awesome 🙂

Anonymous
Not applicable

@Greg_Deckler  

@jdbuchanan71 

@parry2k 

@MFelix 

 

Pooling you all for some much needed support. I would apperetiate any assistance on this issue. Thank you Super Users 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.