DAX Trickery

[Rick_Gregory]

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?

[OwenAuger]

Hi @Rick_Gregory

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

[Rick_Gregory]

That was my original thought as well, but I've had no luck with that in this case.

[OwenAuger · Accepted solution]

@Rick_Gregory

Here's an example of the Dynamic Segmentation approach I was thinking of.

Here's a sample PBIX.

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

[Rick_Gregory]

Ah, that worked! Absolutely brilliant, thank you!

[v-chuncz-msft]

@Rick_Gregory,

Yes, you may add a calculated table that contains the categories.

[Rick_Gregory]

^