Forum Discussion
Calculating Price Difference between SKUs
Hi,
I need help calculating the price difference between pack sizes of the same SKU. Below is the sample output I received:
The values in Price difference are partially correct. There is no discrepancies in data.
Below is the measure I tried to get this output:
CIA PriceDifference =
VAR CRSellUnit = MIN('PRODUCT'[SELL_UNIT])
VAR CRPrice_IV = ([CIA Price Per Unit])
VAR PRSellUnit =
CALCULATE(
MAX('PRODUCT'[SELL_UNIT]),
FILTER(
ALL('PRODUCT'[PACKAGING_CATEGORY],'PRODUCT'[SELL_UNIT],'PRODUCT'[UNIT_VOLUME_ML]),
'PRODUCT'[SELL_UNIT] < CRSellUnit
)
)
VAR PRPrice_FV =
CALCULATE(
([CIA Price Per Unit]),
FILTER(
ALL('PRODUCT'[PACKAGING_CATEGORY],'PRODUCT'[SELL_UNIT],'PRODUCT'[UNIT_VOLUME_ML]),
'PRODUCT'[SELL_UNIT] = PRSellUnit
)
)
RETURN
IF(ISBLANK(PRPrice_FV), BLANK(), PRPrice_FV - CRPrice_IV)
2 Replies
Hi,
I am not sure how the semantic model looks like, but please try something like below if it suits your requirement.
CIA PriceDifference = VAR CRSellUnit = MIN ( 'PRODUCT'[SELL_UNIT] ) VAR CRPrice_IV = [CIA Price Per Unit] VAR PRSellUnit = MAXX ( FILTER ( ALL ( 'PRODUCT'[SELL_UNIT] ), 'PRODUCT'[SELL_UNIT] < CRSellUnit ), 'PRODUCT'[SELL_UNIT] ) VAR PRPrice_FV = CALCULATE ( [CIA Price Per Unit], 'PRODUCT'[SELL_UNIT] = PRSellUnit ) RETURN IF ( ISBLANK ( PRPrice_FV ), BLANK (), PRPrice_FV - CRPrice_IV )
This is the output I am getting.
Here is a snapshot of the model:
