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 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?

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

     

6 Replies

  • 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