cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver II

## Calculating # customers with single or multiple orders per period

I need some help creating a measure, hopefully this is the correct place.

I need to use my dataset to create measures that tell me, within a given period, how many customers placed only 1 order, and how many placed > 1 order.

The columns involved are:

customerID
orderID
orderCountCumulative

The first two columns are self explanatory, the third is a custom column which increments every time a customer places an order. For example the value for a customers first order would be 1, for the second order 2 and so forth. This is based on the unique customerID they have in the customerID column.

My basic approach to this problem has been attempting a measure that does as follows. Note that I have simplified my language to be more readable.This is also only the measure for Single Order Customers, the multiple measures follows the same logic.

var minOrder = CALCULATE(MIN[orderCountCumulative])
var maxOrder = CALCULATE(MAX[orderCountCumulative])

var orderDiff = maxOrder - minOrder

var singleOrderCount = CALCULATE(DISTINCTCOUNT[customerID], FILTER(table,orderDiff=0))

RETURN singleOrderCount

My logic behind this was that anyone placing a single order will have a minimum Order count of 1 and a maximum Order count of 1 therefore the differance is 0. Everyone else placed multiple orders.

The above doesn't however work because rather than look at the orderDiff for individual customerID's, it instead works out the difference using the max and min cumulative orders from ALL records in the given period.

Can someone explain how I would iterate through the table to count the order differance for individual customers OR to explain a simpler/different method for calculating what I want... I feel like I may have over complicated things!

My measure does work if I add customerID to the table rows because then it calculates per customer... this isn't useful though because I want to chat this cannot have the customerID as an axis.

1 ACCEPTED SOLUTION

you talk about a given period, but you seem to be missing a date column in your table. How do you know the period you are talking about?

so putting period aside, I recommend you create a customer lookup table (called customers) that contains a single row for each customer.  Then join the table you have (I will call it Orders) to the customer table using a unique customer ID

I don't believe you need the third data column.

Your first measure then would be (note I haven't tested it but I think it will work)

Cust with 1 order =

sumx(Customers,

if(calculate(countrows(Orders)) = 1,1,0)

)

copy the pattern for the other one.

SUMX is an iterator. It creates a row context over the customer table. It takes one customer at a time. At each customer the CALCULATE function will cause the row context from the customer table to be converted to a filter context.  This then filters the Orders table so only orders for that 1 single customer are visible for the purpose of the calculation (for this one customer). If the answer for the single customer is 1 row, then 1 is added by SUMX. The process then moves to the next customer as SUMX iterates through every customer (one at a time) adding 1 for each customer that matches the rule (If 1 and only 1 row exists).

Hope me that makes some sense. Evaluation context is a complex topic and takes some time to learn.

* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
5 REPLIES 5
New Member

Hi I dont know if this helps, but i did the following:

var
Customers=FILTER(SUMMARIZE(Orders,Orders[customer_id],"c",COUNT(Orders[id])),[c]=2)
Return
COUNTROWS(Customers)

it worked for me.

you talk about a given period, but you seem to be missing a date column in your table. How do you know the period you are talking about?

so putting period aside, I recommend you create a customer lookup table (called customers) that contains a single row for each customer.  Then join the table you have (I will call it Orders) to the customer table using a unique customer ID

I don't believe you need the third data column.

Your first measure then would be (note I haven't tested it but I think it will work)

Cust with 1 order =

sumx(Customers,

if(calculate(countrows(Orders)) = 1,1,0)

)

copy the pattern for the other one.

SUMX is an iterator. It creates a row context over the customer table. It takes one customer at a time. At each customer the CALCULATE function will cause the row context from the customer table to be converted to a filter context.  This then filters the Orders table so only orders for that 1 single customer are visible for the purpose of the calculation (for this one customer). If the answer for the single customer is 1 row, then 1 is added by SUMX. The process then moves to the next customer as SUMX iterates through every customer (one at a time) adding 1 for each customer that matches the rule (If 1 and only 1 row exists).

Hope me that makes some sense. Evaluation context is a complex topic and takes some time to learn.

* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Resolver II

Hi Matt, thanks for that I'm going to test it out now. As for the period there is a datePaid column which is linked to a dateTable, I was planning on just using that as a filter and not factoring it into the measure itself.

Ok, good so this will work with your calendar table on a chart axis for example.

* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Resolver II

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors