Segment by sum of quantity and number of customers per segment

[Anonymous]

Hi Experts,

I have a fact table looking like this:

Orders

date                                                            customerid                       qty

27-02-2019	2	1
27-02-2019	2	6
27-02-2019	3	4
27-02-2019	3	2
27-02-2019	3	4
10-03-2019	1	6
10-03-2019	1	2
10-03-2019	3	2
10-03-2019	1	1

I would like to segment into quantity segments, showing number of customers in each segment. The segments are by quantity and result should like below:

Quantity segment                                                       February                  March

Quantity 1	1	1
Quantity 2	0	2
Quantity 3	0	0
Quantity 4	2	0
Quantity 5 or more!	1	1

The date columns february and march are showing number of customers per segment.

I've been looking at https://www.daxpatterns.com/static-segmentation/ and https://www.daxpatterns.com/dynamic-segmentation/ for inspiration - but I can't seem to get around a way of doing it. Any hints, help or advice to what DAX pattern is correct would highly appreciated.

I should hope to end up with a visual like the one below:

Best wishes and hope - from:

Jon

[v-juanli-msft]

Hi @Anonymous 

Is this problem sloved?

If not, please let me know.

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Maggie,

So kind of you to follow up. Problem is still not solved 😞

What I want to do is:

Create 5 named segments/buckets.

For each row in Orders Table:

      Get value of field Quantity

Then - for visualization: Sum all quantity fields for a customer - and check where that sum fits within the defined segment bucket.. For example:

During week 42, user DanJohnson has 3 orders, 2 with quantity of 3 and one with quantity of 5. In total: quantity 11. 11 fits in to segment5 which has values 5 and above.

Hopefully,

Jon

[Ashish_Mathur]

Hi,

Have you checked my solution?

[Anonymous]

Hi Ashish,

I actully did - and thank you I appreciate your help.

Back then I couldn't make it work - but now.. I'll try again - it looks like it is actually the right thing. I'll check again - and come back 🙂

Jon

[v-juanli-msft]

Hi @Anonymous 

Create columns

year = YEAR(Sheet1[date])

month = FORMAT(Sheet1[date],"MMMM")

Quantity segment = SWITCH(TRUE(),Sheet1[qty]=1,"Quantity 1",Sheet1[qty]=2,"Quantity 2",Sheet1[qty]=3,"Quantity 3",Sheet1[qty]=4,"Quantity 4",Sheet1[qty]>=5,"Quantity 5 or more")

Create measure

Measure = CALCULATE(DISTINCTCOUNT(Sheet1[customerid]),ALLEXCEPT(Sheet1,Sheet1[year],Sheet1[month],Sheet1[Quantity segment]))

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Maggie,

I sorry I didn't make it clear. But the second table in my post is not something I have, but the result I wish.

So - in my model I only have a table called orders (a date table also exists).

- So I need to create segments (Quantity 1,2,3,4 and 5 and above) - and then find out how many customers belong to each quantity segment.

Does it make sense?

Jon

[v-juanli-msft]

Hi @Anonymous 

I don't use the second table as the original table, i use the first table as the original table.

I use the first table from your post, then create measures and columns, thus i can create a matrix visual as the second table in your post by adding some columns and measure in a matrix.

Per your requirement, modify the measure i provide before as below

Measure = CALCULATE(DISTINCTCOUNT(Sheet1[customerid]),ALLEXCEPT(Sheet1,Sheet1[year],Sheet1[month],Sheet1[Quantity segment]))

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Maggie,

Thank you for quick response.

I do not have the quantity segment in my dataset. It needs to be created. 

Segment 1: Quantity = 1

Segment 2: Quantity = 2

Segment 3: Quantity = 3

Segment 4: Quantity = 4

Segment 5: Quantity => 5

To me it looks like the pattern: https://www.daxpatterns.com/dynamic-segmentation/ where a parameter table is containing the segments.

So - for each week, month, quarter, year (every column in my date dimension table) I'd like to answer the question: what is the distribution of customers in each segment, for example how many customer did belong to Segment 1 in January 2019? 

Segments don't exist - they need to be created somehow. 

Jon

[Ashish_Mathur]

Hi,

You may download my PBI file from here.  Given the data ou have shared, the result should be

[v-juanli-msft]

Hi @Anonymous 

Download my file and see details.

Best Regards

Maggie

[v-juanli-msft]

Hi @Anonymous 

See my first post, i update it just now.

Best Regards

Maggie