Forum Discussion

SachinNamdeo-20's avatar
SachinNamdeo-20
Icon for Helper II rankHelper II
3 years ago
Solved

Active customers count

"Dear community, I have 5 years of data at the invoice level. I am trying to calculate the no. of customers who purchased at least 3 times in the current FY and sales quantity of that customers. FY ...
  • johnt75's avatar
    johnt75
    3 years ago

    If you have a date table linked to the fact table you can try

    Active Customers =
VAR SummaryTable =
    ADDCOLUMNS (
        SUMMARIZE ( Sales, Sales[Customer] ),
        "@num months", COUNTROWS ( CALCULATETABLE ( SUMMARIZE ( Sales, 'Date'[Year month] ) ) )
    )
RETURN
    COUNTROWS ( FILTER ( SummaryTable, [@num months] >= 3 ) )
  • SachinNamdeo-20's avatar
    SachinNamdeo-20
    3 years ago
    calculation done
    Active dealer in ficial year =     CALCULATE([Active Customers],DATESYTD(Calendar_new[Date],"03/31"))
FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
3 years ago

hi SachinNamdeo-20 

 

Not sure if i fully get you, you may try to plot a table visual with two measures like this:

3PlusCustomer =
VAR _table =
ADDCOLUMNS(
    VALUES(TableName[Dealer]),
    "OrderCount",
    CALCULATE(COUNTROWS(TableName)),
    "SalesQty",
    CALCULATE(SUM(TableName[sales]))
)
RETURN
COUNTROWS(FILTER(_table, [OrderCount]>=3))

3PlusCustomerSales =
VAR _table =
ADDCOLUMNS(
    VALUES(TableName[Dealer]),
    "OrderCount",
    CALCULATE(COUNTROWS(TableName)),
    "SalesQty",
    CALCULATE(SUM(TableName[sales]))
)
RETURN
CALCULATE(SUM(TableName[sales]), FILTER(_table, [OrderCount]>=3))

 

unable to access your file over the cloud and tried to verify with a simplified sample data:

 

SachinNamdeo-20's avatar
SachinNamdeo-20
Icon for Helper II rankHelper II
3 years ago

Thank you sir for your valuable suggestion