Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

DAX Growth Contribution

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



1 ACCEPTED SOLUTION
Anonymous
Not applicable

// 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
)

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

// 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
Not applicable

Amazing - thanks!

amitchandak
Super User
Super User

@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
Not applicable

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
Not applicable

The calculation should work like this:

SorenAltmann_0-1596717437437.png

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
Super User
Super User

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors