Column to count number of orders for each customer

[Balu15]

Hi,

I'm pretty new to Power BI and struggling to find an answer online, hoping someone can help me here as I feel Like I am missing something obvious.

I'm trying to create a custom column that counts the total number of Orders a Customer makes. I am able to get the count using a Matrix. However, I would also like to group the number of orders, for example:

1 order
2 - 6 orders
7 - 12 orders
13 - 24 orders
25 - 90 orders
90+

I have been able to achive this using the two measures but due to the limitation of using measures in visulisations I was hoping this would be possible with two columns instead.

The current column I am using (as per other threads and suggestions) is not giving the correct count:

Orders Per Customer COLUMN =

CALCULATE(

   DISTINCTCOUNT('salesorder'[Order ID]),

   ALLEXCEPT('salesorder', 'salesorder'[contactid])

   )

As you can see it is counting the number of Order IDs (second row) against each Product (third row) not for the Customer (first row). The result I was expecting is 6 orders not 20.

Thanks,

[danextian]

Hi @Balu15 

I don't see anything wrong with your calc column. It should be returning the distinct count or orders for each contact id...per row of contact id that is. However, what you're doing is aggregating in the viz the result of of the calc column and contact id can have more than one rows in your table. The calc column returns the same value for each row of the distinct contact id. However simply summing the result will give you a wrong number. Multiply that  with the number of rows a contact id has and that is what you're most likely seeing now.

You can try

SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( 'salesorder', 'salesorder'[contactid] ),
        "@count", CALCULATE ( MAX ( 'salesorder'[order count per contact id] ) ) --max order count for each contact id, min/max/average dont matter as the numbers are the same
    ),
    [@count]
)

[Balu15]

Thanks. That makes sense. With regards to your solution would 'salesorder'[order count per contact id] refer to my custom column called 'Orders Per Customer COLUMN'? I have tried to run this but it just seems to keep my Power BI stuck on 'Working on it' for a very long time.

[danextian]

It refers to that. Did you create a measure?

[Balu15]

Hi @danextian 

The measure is giving the total number of rows 23. I have a measure that gives me the correct result:

Orders Per Customer MEASURE = CALCULATE(DISTINCTCOUNT(salesorder[Order ID]))

and another to group per number of orders:

Order Group =
SWITCH(
TRUE(),
[Orders Per Customer MEASURE] = 1, "1 order",
[Orders Per Customer MEASURE] >= 2 && [Orders Per Customer MEASURE] <= 6, "2 - 6 orders",
[Orders Per Customer MEASURE] >= 7 && [Orders Per Customer MEASURE] <= 12, "7 - 12 orders",
[Orders Per Customer MEASURE] >= 13 && [Orders Per Customer MEASURE] <= 24, "13 - 24 orders",
[Orders Per Customer MEASURE] >= 25 && [Orders Per Customer MEASURE] <= 90, "25 - 90 orders",
[Orders Per Customer MEASURE] > 90, "Above 90 orders"
)

I would like to be able to use visualizations to show this data so that is why I was wondering if it was possible to do as a custom column. Correct me if I'm worng but as far as I'm aware I'm limited with using measures in visualizations.

[danextian · Accepted solution]

Did you use this as a measure?

SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( 'salesorder', 'salesorder'[contactid] ),
        "@count", CALCULATE ( MAX ( 'salesorder'[order count per contact id] ) ) --max order count for each contact id, min/max/average dont matter as the numbers are the same
    ),
    [@count]
)

I dont see why this should take a very long time create as this is not stored in-memory. If a calculated column then that makes sense

[ryan_mayu]

could you pls provide some sample data?

[Balu15]

Thanks for the reply. Forgive my ignorance but I'm not sure I would be able to as the data is being pulled straight from our CRM system. What did you want to see specifically from the sample data? I can try to recreate in excel