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. 😁