calculate number of customer based on number of orders

[Anonymous]

Hi, 

My task is to Calculate Total of customers whom: 

made orders 1-5 

made orders 6-10 

made orders 11+

The result that I expected to see how many customer whom made 1-5 orders, 6-10, 10+ of each month

This is the result that I expect. I made it in Tableau. 

here is the file for you to have better understanding. 

https://we.tl/t-3SjZ91cTEM

Could you show me which calculation formula should I use to segment customer like this?

Data I use is: sample_superstore

Hope to hear from you and Thank you for reading!! 

[DouweMeer]

I think the easiest way is to create a table with date periods as input

Name   Start count   End count

M1        1                   5

M2        6                   10

Then create a table reference that has as output something like :

Cust 1 - PO5 

Cust 2 - PO6 

Cust 3 - PO7 

Cust 1 - PO 8 

Then create a new VAR with as table reference the previous created table. Add a column with count PO for cust:

Cust 1 - PO5 - Count 2

Cust 2 - PO6 - Count 1

Cust 3 - PO7 - Count 1

Cust 1 - PO8 - Count 2

Then it would be a 'simple' distinct selectcolumn on Cust with a filter similar to the set date periods. It will take you some time probably, but it would be a solution to your problem.

[Anonymous]

Hi, 

Thank you for your reply, 

Could you make it in a workbook and send it to me? 

cause it is quite vague for me to imagine.

[DouweMeer]

All filesharing possibilities that go outside the company is limited thus I wouldn't be able to share it with you. Let me see if I can create an expression as example...

[Anonymous]

You can share it when you back home. 

I am using dta: sample_superstore

[DouweMeer]

Hmm, stuffing it in a measure is the better solution. I've tested the measure for multiple months and it seems to be working. Still, the intermediate table references mentioned are still there.

M1 =

VAR a1 = 1

VAR a2 = 5

VAR t1 =

SELECTCOLUMNS(

    'PO table'

    , "Cust" , 'PO table'[Customer]

    )

VAR t2 =

distinct (

    SELECTCOLUMNS(

        t1

        , "Cust2" , [Cust]

        , "Count2" , countx ( filter ( 'PO table' , 'PO table'[Customer] = [Cust] ) , 'PO table'[Customer] )

        )

    )

RETURN

countx (

    filter (

        t2

       , [Count2] >= a1 && [Count2] <= a2

       ) 

    , [Cust2]

    )

Table with measureThe measureThe 'PO Table'Intermediate result of the measure

[Anonymous]

Hi, 

Thank you for spending time to help me!

if you got the workbook that is much better for me. 

Cause seeing from the work that you do, I wonder what is PO?

[DouweMeer]

As mentioned, Power BI doesn't support other file type to be uploaded than photos and videos, so I can understand your wish but I can't execute it :).

PO would be the abbreviation of Purchase Order. 

[Anonymous]

How about Cust1 , Cust2? How could I able to substitute. 

In my case, I calculate by Customer ID. 

[DouweMeer]

The Cust should be the Customer ID :).

I wasn't able to download your pbix due to the IT settings over here.