Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
)
// 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
)
Amazing - thanks!
@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])
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.
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |