Help - How to group customers by # of orders at certain amounts and over time

[BabyBinki821]

Hi

So I a stuck on this. I am trying to basically create a bar chart showing customers who have made 2 or more orders, 10 or more, 20 or more, 50 or more and 100 or more and have it update weekly. I have all the necessary data tables already set up (including date table).

My first approach was creating a table with unique customer ID and the total orders, which was successful - Image below.

I then used this calculation  to get # of customers who made at least 2 orders -

Measure = Calculate(DISTINCTCOUNT('Customer Orders'[EC2 ID #]),'Customer Orders'[Total Orders]>1)

The number I am getting is way off, not too mention its static (not changing over in time).

What am I doing wrong?

[BabyBinki821]

Also, sorry not to spam but do I even need a table? Could I create a DAX measure to basically say find me the number of times a unique ID order 2 or more and then put in bar chart?

[BabyBinki821]

Also here is an image of my main data table down below. I am basically trying to create this bar chart with each stack representing an order threshold.

[Jihwan_Kim]

Hi,

I am not sure how your data model looks like, but if you want to create dynamic grouping type of visaul, one of ways to solve it is having a grouping table like below.

I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

Orders total: = 
SUM( Data[Orders] )

Customers count: =
CALCULATE (
    COUNTROWS ( Customer ),
    FILTER (
        Customer,
        COUNTROWS (
            FILTER (
                GroupTable,
                GroupTable[Min] <= [Orders total:]
                    && GroupTable[Max] >= [Orders total:]
            )
        ) > 0
    )
)

[BabyBinki821]

How did you create the cutoff for the group numbers?

[tamerj1]

Hi @BabyBinki821 
Dynamic segmentation can work only for small-medium size data. The question is: if you summarize your orders table by customer and date, how many rows you get? Regarding your measure, it should give the correct number of customers that made more than 2 orders. There is no doubt about that. But how are you displaying this number? Is it just a card visual?

[BabyBinki821]

I have over 40,000 rows

[BabyBinki821]

And in terms of the measure, if I filter the column I get 646 customers that made 2 o more orders but the measure itself in the bar chart only returns ~240