DAX Growth Contribution

[Anonymous]

I am building a dashboard in PowerBI and have various semi-complex DAX Measures I need to calculate.

Challenge #1 : Growth Contribution

My report is looking at sales in cheese across months and cheese segments. The database contains information at product level and segments are calculated on the fly.
I have already calculated basic things like sales growth, share etc and now need to add a measure for the contribution to sales growth that one segment makes by month.

So..

First I need to calculate the growth by segment (ValueChg)
Then I need to add up all segments that have positive growth and all segments that have negative growth (ValueChgPosSum , ValueChgNegSum)

The contribution of any given segment is then:

If ValueChg>0 then ValueChg / ValueChgPosSum Else ValueChg / ValueChgNegSum

[Anonymous · Accepted solution]

// Assumptions:
// [Abs Growth] - measure that for any period shows
// 		  the growth in sales (in the currency)
// 		  that happened between this period and
//		  the prior one
// Segment  - an attribute that must exist in a dimension;
//   	      the most logical way would be to store it in the
//	      Products dimension
// 

[Segment Contribution] =
if( HASONEFILTER( Products[Segment] ),

	var __segmentAbsGrowth = [Abs Growth]
	var __contribution =
		switch( true(),
		
			__segmentAbsGrowth > 0,
			
				// Get the total growth for segments
				// that have growth > 0.
				var __totalGrowth = 
					SUMX(
						all( Products[Segment] ),
						var __growth = [Abs Growth]
						RETURN
							( __growth > 0 ) * __growth
					)
				return
					DIVIDE( __segmentAbsGrowth, __totalGrowth ),
					
					
			__segmentAbsGrowth < 0,
				// Get the total growth for segments
				// that have growth > 0.
				var __totalGrowth = 
					SUMX(
						all( Products[Segment] ),
						var __growth = [Abs Growth]
						RETURN
							( __growth < 0 ) * __growth
					)
				return
					DIVIDE( __segmentAbsGrowth, __totalGrowth ),
				
			// If segment growth is 0, return 0? Return Blank?
			// Adjust this calculation to suit your needs.
			0
		)
	return
		__contribution
)

[Anonymous]

Amazing - thanks!

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format?

refer for

SEGMENTATION

https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-power-query/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

Try

https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

Diff

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

[Anonymous]

Here is a small sample of data : https://we.tl/t-b6ECoYRcfH

When I refer to "segment" I mean simply different groups of product.

In the attached, I want a row in the table to indicate the value growth contribution by period.

Value Chg (change) has already been calculated.

[Anonymous]

The calculation should work like this:

Each segment can have a positive or negative growth (Value Chg).
The growth contribution is a % of all the segments have the same (positive or negative growth).

The DAX expression should work with filters as well, so I could display on SEG B and see the 33% contribution for Per 1.

[Fowmy]

@Anonymous 

Can you share some sample data to work out the measures?

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn