Forum Discussion

Rick_Gregory's avatar
Rick_Gregory
Frequent Visitor
8 years ago
Solved

DAX Trickery

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 ...
Need Help
Tips and Tricks
  • OwenAuger's avatar
    OwenAuger
    8 years ago

    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

     

OwenAuger's avatar
OwenAuger
Super User
8 years ago

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's avatar
Rick_Gregory
Frequent Visitor
8 years ago

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

  • OwenAuger's avatar
    OwenAuger
    Super User
    8 years ago

    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's avatar
      Rick_Gregory
      Frequent Visitor
      8 years ago

      Ah, that worked! Absolutely brilliant, thank you!