Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Need to manipulate context-based values

I have a dataset of software revenue by market and submarket. It spans multiple years, and I'm calculating year-over-year (YoY) change metrics. One of these involves the square of the YoY change in market share by vendor. I need to do this based on the current filter context. I'm able to calculate market share by vendor and year in a matrix visual, but I can't figure out how to compute the YoY difference, square them, and sum them up. I assume it includes a SUMX - FILTER combination, but I can't figure out the right syntax.

Again, what I need to calculate is the sum of the squares of the YoY change in market share, by filter context.

Here is a link to a .pbix file with sample data: https://1drv.ms/u/s!AgIj2L_vt8Wrg_B5BkUMVGMgKTKReA?e=0pKybx 

 

The data spans 2 years, 2018 and 2019. What I need to do is:

1) Calculate the market share for each vendor, by year, for the Market / Subsegment combination currently set by the filter context. (This works currently.)

2) Calculate the YoY market share difference (2019 Market Share - 2018 Market Share) for each vendor

3) Square each difference value from step 2 and sum them up for each column (filter context)

  • OwenAuger's avatar
    OwenAuger
    5 years ago

    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

2 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    5 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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 goal

      Thanks again for showing me the way. Little by little, I'm getting more comfortable with multi-step DAX calculations. 😁