How can I calculate number of clients who ordered greater than 1000 times?

[Kira_Net]

 I need to calculate
1. The number of clients who ordered more than 1000 times last week

2.The total volume of orders (for those clients who ordered more than 1000 times)
3.The percent to total (The contribution of those clients order to the total volume.)

The expected result should be a scalar value and visualized on a scorecard.

My data table looks like

Client	Order Date
A	04/21/2022
B	04/12/2022
B	03/17/2022
C	04/28/2022
A	04/29/2022
A	04/27/2022
A	04/24/2022
C	04/22/2022
C	04/28/2022

[Anonymous]

Hi @Kira_Net ,

Please refer to the measures.

Measure number of clients>1000 =
COUNTROWS (
    FILTER (
        VALUES ( OrderTable[Client] ),
        CALCULATE (
            COUNTROWS ( OrderTable ),
            ALLEXCEPT ( OrderTable, OrderTable[Client], OrderTable[OrderDate] ),
            DATESBETWEEN ( OrderTable[OrderDate], TODAY () - 7, TODAY () )
        ) > 1000
    )
)

Measure_total= Calculte(sum('OrderTable' [orders]),filter(all('OrderTable'),datesbetween('OederTable'[OederDate],Today()-7,Today())&&[Measure number of clients>1000)

Measure=var _total=calculate(sum('OrderTable'[orders]),filter(all('OrderTable'),DATESBETWEEN ( OrderTable[OrderDate], TODAY () - 7, TODAY () )))
var _bigthan1000=[Measure total]
return
Divide(_bigthan,_total)

If I have misunderstood your meaning, please provide your pbix file without privacy information and desired output.

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[danextian]

Hi @Kira_Net ,

I am not sure how you divide your period by week but these measures should return the order count per client (assuming each row count in the table is equivalent to an order) and the number of clients withmore than 1000 orders:

Order Per Client = 
CALCULATE ( COUNT ( _Table[Order Date] ), ALLEXCEPT ( _Table, _Table[Client] ) )


Clients with orders above 1000 =
SUMX (
    SUMMARIZE (
        VALUES ( _Table[Client] ),
        "Order", IF ( [Order Per Client] > 100, 1 )
    ),
    [Order]
)