Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
The problem:
Simple, I need to bucket customer counts based on their growth % from the previous month (ie How many customers grew their sales 10-15%,16-20%, etc).
In my fact table, there are 3 columns: Date, Total Sales, & Customer ID. I do have a date table.
What I've been trying to do is create a disconnected table that shows Date, Customer ID,Sales, & Previous Months Sales (using a calculated column) so I could then use the growth percentage on an X-axis using a customer count of as my values.
Now, I know I can use a PreviousMonth measure to achieve this, but I belive I need this in the form of a column in this case so I can use it on an axis.
Thoughts?
Solved! Go to Solution.
Here's an example of the Dynamic Segmentation approach I was thinking of.
I used a pattern similar to one presented here:
https://www.sqlbi.com/articles/optimizing-duplicated-dax-expressions-using-variables/
Data model:
Then the Segmentation measures are:
Customers by Segment = SUMX ( Segments, COUNTROWS ( FILTER ( VALUES ( Sales[Customer ID] ), VAR MoMPct = [Sales MoM %] RETURN NOT ( ISBLANK ( MoMPct ) ) && MoMPct >= Segments[Lower] && MoMPct < Segments[Upper] ) ) )
Sales by Segment = SUMX ( Segments, SUMX ( VALUES ( Sales[Customer ID] ), VAR MoMPct = [Sales MoM %] VAR SalesMeasure = [Sales] RETURN IF ( NOT ( ISBLANK ( MoMPct ) ) && MoMPct >= Segments[Lower] && MoMPct < Segments[Upper], SalesMeasure ) ) )
Both measures assume Segments don't overlap, otherwise they would have to be written more like the versions on DAX Patterns.
Output of measures looks like:
Regards,
Owen
After reading your question, I think this is a perfect case for dynamic segmentation!
https://www.daxpatterns.com/dynamic-segmentation/
This would involve a disconnected table specifying the % ranges, then creating a segmentation measure using the pattern at the above link.
I don't have time to work up an example right now but suggest you give it a go & post back if needed.
Generally I prefer a dynamic approach with measures that will respond to filter context, rather than a calculated table that may solve one instance of a problem. For example, later on you might want to segment customers based on growth in a certain type of sales or in certain regions etc.
Best regards,
Owen
That was my original thought as well, but I've had no luck with that in this case.
Here's an example of the Dynamic Segmentation approach I was thinking of.
I used a pattern similar to one presented here:
https://www.sqlbi.com/articles/optimizing-duplicated-dax-expressions-using-variables/
Data model:
Then the Segmentation measures are:
Customers by Segment = SUMX ( Segments, COUNTROWS ( FILTER ( VALUES ( Sales[Customer ID] ), VAR MoMPct = [Sales MoM %] RETURN NOT ( ISBLANK ( MoMPct ) ) && MoMPct >= Segments[Lower] && MoMPct < Segments[Upper] ) ) )
Sales by Segment = SUMX ( Segments, SUMX ( VALUES ( Sales[Customer ID] ), VAR MoMPct = [Sales MoM %] VAR SalesMeasure = [Sales] RETURN IF ( NOT ( ISBLANK ( MoMPct ) ) && MoMPct >= Segments[Lower] && MoMPct < Segments[Upper], SalesMeasure ) ) )
Both measures assume Segments don't overlap, otherwise they would have to be written more like the versions on DAX Patterns.
Output of measures looks like:
Regards,
Owen
Ah, that worked! Absolutely brilliant, thank you!
Yes, you may add a calculated table that contains the categories.
^
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
103 | |
77 | |
66 | |
63 |
User | Count |
---|---|
142 | |
105 | |
102 | |
81 | |
68 |