Forum Discussion
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
// 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 )
6 Replies
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-visualizationTry
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])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.
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.
// 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 )
