How can I calculate the Number of client who ordered more than 1000 times last week?

[Kira_Net]

Hello there,
I am having hard time to configure the number of clients who ordered more than certain number of times (1000 in my case). I have a client column and date table. The total order would be calculated by counting rows. Can some one help me please?

[speedramps]

amirabedhiafi

I think your solution counts orders.  When Kira_Net wants the number of clients with > 1000 orders.   😀

[amirabedhiafi]

If you pick up a table visual with the Client and the measure it will give you the number of orders by client.

Adding a filter on the visual to display only the clients with more 1000 orders for the last week will give the expected result based on what the user defined in the begining.

Now the user @Kira_Net  in his last comment is giving another need which is a card with a specific calculation.

@Kira_Net  can you provide exactly the expected visual output ?

@speedramps  without input and expected output, I am a little bit lost...

[Kira_Net]

I need the number of clients who ordered greater than 1000 times in last week. the expectedout put will be a scalar value.

@amirabedhiafi @speedramps 

[speedramps]

Hi again Kira_Net

 * Try use this measure with a filter on the visual for the current week or month, not all months *

I've helped you so please help me.

Please click the thumbs up and Accept As Solution to leave kudos.

Number of big clients =

// get a summary list of all clients and number of sales

VAR allcustomers =

SUMMARIZECOLUMNS(

Sales[Client],

"Volume",

COUNTROWS(Sales)

)

// reduce the summary to just clients with > 1000 sales

VAR bigclients = FILTER(allcustomers,[Volume] > 1000)

RETURN

// count the number of clients on the list

COUNTROWS(bigclients)

[Kira_Net]

Hmmm, But I wanted to display in a score card and I wanted a dax formula. I found this formula 
COUNTROWS(FILTER(SUMMARIZE('ORDER_TABLE', 'ORDER_TABLE[Client], "Count", Distinctcount(ORDER_TABLE[OrderID])),[Count]>1000))

But this doesn't give me the value for last week.

[amirabedhiafi · Accepted solution]

Try this measure :

Measure = COUNTROWS (

FILTER (

VALUES ( OrderTable[Client] ),

CALCULATE (

COUNTROWS ( OrderTable ),

ALLEXCEPT ( OrderTable, OrderTable[Client], OrderTable[OrderDate]),

DATESBETWEEN(OrderTable[OrderDate],TODAY()-7, TODAY())) >10000

)

)

[Kira_Net]

@amirabedhiafi What do I do to add those volumes? Lets say we have 3 clients who ordered morethan 1000 according to this dax formula, and I want to add those volume. Please.

[amirabedhiafi]

It is simple try to SUM the quantity like below : 

CalculateVolume = SUM(OrderTable[Volume])

and put it in a card near to the other one, it will only show the volume count for the clients who ordered more than 10000

[Kira_Net]

@amirabedhiafi I want to sum the order value for those who ordered greater than 1000 only.

[amirabedhiafi]

What do you mean by volume? is it the quantity ordered by each client ?

[Kira_Net]

Thank you! This works perfectly.

[amirabedhiafi]

Glad to help! Keep up the good work

[speedramps]

Hi Kira_Net

Try use this measure with a filter for the current week.

I've helped you so please help me.

Please click the thumbs up and Accept As Solution to leave kudos.

Number of big clients =

// get a summary list of all clients and number of sales

VAR allcustomers =

SUMMARIZECOLUMNS(

Sales[Client],

"Volume",

COUNTROWS(Sales)

)

// reduce the summary to just clients with > 1000 sales

VAR bigclients = FILTER(allcustomers,[Volume] > 1000)

RETURN

// count thw number of clients on the list

COUNTROWS(bigclients)

[Kira_Net]

@speedramps Thank you, but this only gives me the value for all time. I want to return only last week(weekly) and last month (last Month). Can you help me with that?

[Kira_Net]

@amirabedhiafi  Sure, my data looks like this.

[amirabedhiafi]

Create a measure where you retrieve the count for all the clients starting from last week until today : 

Count of Orders = CALCULATE(COUNT(OrderTable[Client]),DATESBETWEEN(OrderTable[OrderDate],TODAY()-7, TODAY())

)

then in the visual you can apply a condition on the measure above, is greater than or equal 1000 like below : 

[amirabedhiafi]

Can you share sample of the data you are working on with the needed columns ?