Hi Fellow Power BI Users -
I’m trying to use this method for a Part Number context (rather than date) and using YTD numbers to accomplish the date portion. Unfortunately the total that the following delivers bears no similarity to the context table. The variables are working through some criteria so that the price change is only evaluated if it passes those criteria. Can you spot what I’m doing wrong?
Would appreciate any guidance you can offer!
Peter
Price Impact: Sell Price Change $ =
VAR TooBigTest = IF([% Change in Quantity] < 4, "Pass", "Fail")
VAR TooSmallTest = IF([% Change in Quantity] > -0.75, "Pass", "Fail")
VAR SellPriceChange =
SWITCH( TRUE(),
[Quantity YTD] = 0, 0,
[PYTD Quantity] = 0, 0,
TooBigTest = "Fail", 0,
TooSmallTest = "Fail", 0,
([Avg Sell Price $ (Part)] - [PYTD Avg Sell Price $ (Part)]) * [Quantity YTD] )
VAR TotalSellPriceChange = SUMMARIZE(Sales, Sales[Part#/Item#], "SellPriceChg", SellPriceChange)
RETURN
IF( HASONEVALUE(Sales[Part#/Item#]), SellPriceChange, SUMX(TotalSellPriceChange, [SellPriceChg]) )
The results table I’m seeing is as follows; although this result shows 0 it is not always zero depending on the filters applied:
The first thing to note here is that if you get the formula right on this you shouldn’t require the HASONEVALUE part after RETURN.
IF( HASONEVALUE(Sales[Part#/Item#]), SellPriceChange, SUMX(TotalSellPriceChange, [SellPriceChg])
What you should be aiming to do here is just have the SUMX do the work and place the SUMMARIZE inside of it.
SUMX(
SUMMARIZE(Sales, Sales[Part#/Item#], “SellPriceChg”, SellPriceChange),
[SellPriceChg])
This is how it should be and will be much easier to audit. If you can get the correct results at each row here, then the total should calculate correctly as well.
Can you see if just this makes any difference? I’m not 100% sure but I’m interested in what results it gets.
My other guess as to why it’s not working is because of this
This is evaluating to fail and then automatically turning this into 0 because of this part of the formula
You probably want to wrap a HASONEVALUE around this maybe so that it doesn’t get calculated at the total level.
See how you go with these ideas.
I’ve moved the HASONEVALUE up to the pass/fail tests as you suggested, which has made a difference to the results but not exactly what we were looking for!
Updated formula:
Price Impact: Sell Price Change $ =
VAR TooBigTest = IF([% Change in Quantity] < 4, "Pass", "Fail")
VAR TooSmallTest = IF([% Change in Quantity] > -0.75, "Pass", "Fail")
VAR SellPriceChange =
SWITCH( TRUE(),
[Quantity YTD] = 0, 0,
[PYTD Quantity] = 0, 0,
HASONEVALUE(CX_SALES_ORDER_DET[Part#/Item#]) && TooBigTest = "Fail", 0,
HASONEVALUE(CX_SALES_ORDER_DET[Part#/Item#]) && TooSmallTest = "Fail", 0,
([Avg Sell Price $ (Part)] - [PYTD Avg Sell Price $ (Part)]) * [Quantity YTD] )
VAR TotalSellPriceChange = SUMMARIZE(CX_SALES_ORDER_DET, CX_SALES_ORDER_DET[Part#/Item#], "SellPriceChg", SellPriceChange)
RETURN
SUMX(TotalSellPriceChange, [SellPriceChg])
This is what it looks like if I add a different part into the results table … just bizarre variation:
Sorry for the delay.
I’m not sure I said place it there.
I meant more here. To me this is where the issue is.
It’s reading the total value in that column and then being calculated based on that. You don’t want this.
Also I don’t see the other adjustment I mentioned above regarding the SUMMARIZE etc.
I’m interesting if they make a difference.
With total you really need to think deeply about the context. With the variables you have they are being work out first (not at every row) like how you want with the SWITCH statement.
See how you go with these changes.
If this still doesn’t get what you need I would have to suggest sharing the example here so I can have a look at it and test more
I’ve replicated the problem using one of the tables from the AdventureWorks dataset (Price change by product.pbix (3.8 MB)), and have left out the size tests for now.
One of the issues seems to be that my formula is performing the evaluation for the column totals and then multiplying it by the number of rows in the table. I’m afraid I can’t see why it would do that as it is now very closely replicating your example.
The formula that I originally shared also seems to have some issues around whether or not it adopts all the filters for the context, but I guess that’s probably to do with the set up of my data model. If you can help me with the row multiplication issue then I might be able to fix the rest.
FYI … you asked whether the adjustment regarding the SUMMARIZE would make a difference and it doesn’t seem to make any change.
Thanks for all the help, and Merry Christmas!
This is all you need
Sell Price Change (corrected total) =
SUMX(
SUMMARIZE( aos_products_quotes, aos_products_quotes[product_id] ),
( [Avg Sell Price] - [PY Avg Sell Price]) * [Total Quantity] )
The variables are the issue here, because they evaluate first, before you get into the iteration within the SUMX.
It’s something to be careful on when working out these totals.
I just played around with a few ideas and then landed on this simpler formula above and it seems to get the right answer.
That’s nailed it! Thanks!
