Forum Discussion

SammyNed's avatar
SammyNed
Helper I
5 years ago
Solved

Cumulative Sum

Hi team,

 

I could really use some help. i have a data table similar to that below basically showing Customer ID's and Dates that they placed an Order. I want to create a graph as below that basically shows the sum of the Loyal Customers over a time period. A Loyal Customer is someone who orders more than twice.

 

So in March we should only have 1 loyal customer (customer A with 3 orders).

In April we should have 2 loyal customers (customer A with a total of 4 orders and customer C with a total of 3 orders).

 

I have tried  the following DAX but it isnt cummulative:

 

Loyal Customers = var _CustomerOrders =
SUMMARIZE( orders, orders[cst_id],  "How Many Orders",  COUNTROWS(VALUES(orders[cst_id])))
return
COUNTROWS(FILTER( _CustomerOrders, [How Many Orders]>2) )
 
Please help
 

 

 

 

  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

     

    Loyal Customers count =
    VAR _currentmonthyear =
    MAX ( Dates[End of Month] )
    VAR _untilcurrentmonthyeartable =
    FILTER ( ALL ( Sales ), Sales[Order Date] <= _currentmonthyear )
    VAR _customerscounttable =
    GROUPBY (
    _untilcurrentmonthyeartable,
    Sales[Customer ID],
    "@customerscount", SUMX ( CURRENTGROUP (), 1 )
    )
    VAR _loyalcustomerstable =
    FILTER ( _customerscounttable, [@customerscount] > 2 )
    RETURN
    COUNTROWS ( _loyalcustomerstable )

     

     

    Link to the PBIX file 

     

     

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

     

    Loyal Customers count =
    VAR _currentmonthyear =
    MAX ( Dates[End of Month] )
    VAR _untilcurrentmonthyeartable =
    FILTER ( ALL ( Sales ), Sales[Order Date] <= _currentmonthyear )
    VAR _customerscounttable =
    GROUPBY (
    _untilcurrentmonthyeartable,
    Sales[Customer ID],
    "@customerscount", SUMX ( CURRENTGROUP (), 1 )
    )
    VAR _loyalcustomerstable =
    FILTER ( _customerscounttable, [@customerscount] > 2 )
    RETURN
    COUNTROWS ( _loyalcustomerstable )

     

     

    Link to the PBIX file 

     

     

    • SammyNed's avatar
      SammyNed
      Helper I
      4 years ago

      Hi Jihwan_Kim 

       

      Thank you for your help in the first problem.

       

      I was hoping you could help me adjust the Loyalty value. So instead of a fixed 2 orders making a loyal customer, i want to put a slicer on the page where the user can decide what constitutes a loyal customer, and then the slicer value can be used in the measure you described above.

       

      So if the user chooses 7, then a loyal customer will be anyone with more than 7 orders.

       

      The slicer should range from 1 to the maximum amount of orders by 1 single customer.

      I created a table to summarize the Sales tables by ID and Count of ID (which is the number or orders).

       

      Is this possible?

       

      Thanks in advance

      Sammy_Ned

       

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        4 years ago

        Hi,
        In my opinion, I think you can create a slicer by using "New Parameter" UI.

        Then, in the measure, instead of 2, replace it with "selectedvalue( newparameter slicer value)".

        Sorry that I could not find the sample pbix file, so I only could write like the above.

        Thanks.