Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 ?
Solved! Go to 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 ) )
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
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 Qty | Sum of Avg Sales Value | Sum of Stock Value | Months Cover SKU | Target Qty | Target Value |
Product 38 | 150.50 | 345 | $74,755 | $171,366 | 2.292 | 602 | $299,021 |
Product 107 | 103.33 | 347 | $25,834 | $86,751 | 3.358 | 413 | $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 ) )
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 ;
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 ) )
Pooling you all for some much needed support. I would apperetiate any assistance on this issue. Thank you Super Users 🙂
User | Count |
---|---|
128 | |
108 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |