Forum Discussion
Need to manipulate context-based values
Hi Anonymous
If I've understood the requirements correctly, you want the squared YoY differences in market share summed by Vendor.
I would suggest creating these measures:
Vendor Market Share PY = CALCULATE ( [Vendor Market Share], SAMEPERIODLASTYEAR ( 'Market Data'[Year].[Date] ) // 'Market Data'[Year].[Date] is used since there is an automatic // date hierarchy on the 'Market Data'[Year] column. // If you introduce a separate Date table, // you should instead use the Date column from that table ) Vendor Market Share YoY = VAR CurrentYear = [Vendor Market Share] VAR PriorYear = [Vendor Market Share PY] RETURN CurrentYear - PriorYear // You may want force the measure to return blank if PriorYear values don't exist // I haven't worried about this for now Sum of Squared Vendor Market Share YoY = SUMX ( VALUES ( 'Market Data'[Vendor] ), [Vendor Market Share YoY] ^ 2 )Do these give the expected result?
Regards,
Owen
Super UserHi Anonymous
If I've understood the requirements correctly, you want the squared YoY differences in market share summed by Vendor.
I would suggest creating these measures:
Vendor Market Share PY =
CALCULATE (
[Vendor Market Share],
SAMEPERIODLASTYEAR ( 'Market Data'[Year].[Date] )
// 'Market Data'[Year].[Date] is used since there is an automatic
// date hierarchy on the 'Market Data'[Year] column.
// If you introduce a separate Date table,
// you should instead use the Date column from that table
)
Vendor Market Share YoY =
VAR CurrentYear =
[Vendor Market Share]
VAR PriorYear =
[Vendor Market Share PY]
RETURN
CurrentYear - PriorYear
// You may want force the measure to return blank if PriorYear values don't exist
// I haven't worried about this for now
Sum of Squared Vendor Market Share YoY =
SUMX (
VALUES ( 'Market Data'[Vendor] ),
[Vendor Market Share YoY] ^ 2
)
Do these give the expected result?
Regards,
Owen
Hi OwenAuger ,
I applied your suggested solution, and it's close to what I was looking for. However, I believe the difference in market share has an issue. It looks like the measure [Vendor Market Share] is applied across all years, so if I assign it to the variable CurrentYear, it is incorrect. That's easily fixed, though, since the combined market share minus PriorYear = CurrentYear.
I spent some more time looking through what I was trying to accomplish (which I should have done before reaching out), and I have a solution that is easier for me follow. I'm still getting the hang of "thinking in DAX", and your examples really help me do that. Here's what I came up with:
Revenue =
SUM ( 'Market Data'[Revenue - USD] )
// Basic revenue sum
// I ended up not using this
2018 Revenue =
CALCULATE(SUM('Market Data'[Revenue - USD]),
'Market Data'[Year].[Year] = 2018)
// Revenue filtered for 2018
2019 Revenue =
CALCULATE(SUM('Market Data'[Revenue - USD]),
'Market Data'[Year].[Year] = 2019)
// Revenue filtered for 2018
2018 Vendor MarketShare =
DIVIDE([2018 Revenue],
CALCULATE([2018 Revenue],
ALLSELECTED('Market Data'[Vendor])), 0)
// 2018 revenue measure per vendor divided
// by total revenue for filter context
2019 Vendor MarketShare =
DIVIDE([2019 Revenue],
CALCULATE([2019 Revenue],
ALLSELECTED('Market Data'[Vendor])), 0)
// 2019 revenue measure per vendor divided
// by total revenue for filter context
YoY Vendor MS Change =
[2019 Vendor MarketShare] - [2018 Vendor MarketShare]
// Simple year-over-year difference in market share
Sum of MS Change Squared =
SUMX(
VALUES('Market Data'[Vendor]),
(100*[YoY Vendor MS Change])^2
)
// Sum of squares of market share changes
// This was my goalThanks again for showing me the way. Little by little, I'm getting more comfortable with multi-step DAX calculations. 😁