Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
"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 is an indian fiscal year".
I have this type of data table :-
This is my pbi file link if you have any issue with this then you to this link for visiting data:-
Solved! Go to Solution.
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 ) )
Hi @SachinNamdeo-20
Please refer to amended sample file with the solution
Active Customers =
COUNTROWS (
FILTER (
GROUPBY (
SUMMARIZE (
CUBE_INVOICE,
CUBE_INVOICE[SOLD TO PARTNER.PARTNER CODE],
Calendar_new[YM]
),
CUBE_INVOICE[SOLD TO PARTNER.PARTNER CODE],
"@Months",
SUMX ( CURRENTGROUP ( ), 1 )
),
[@Months] >= 3
)
)
Thank you sir for your valuable suggestion
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:
Thank you sir for your valuable suggestion
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 ) )
Thank you sir for your valuable suggestion
"Sir IF we want the same calculation of your given measure on ficial year how would we can, please suggest".
DATESYTD(Calendar_new[Date],"03/31")
Sir IF we want the same calculation on ficial year how would we can, please suggest
DATESYTD(Calendar_new[Date],"03/31")
"Thank you for your valuable suggestion but it gives me blank value, Please suggest me any other measure i also mention my pbi file above"
Your date table isn't linked to the invoice table. You need to link it on the appropriate field.
"Sorry for mistake sir in this file date is linked with cube invoice but it also return blank value"
https://drive.google.com/file/d/1QPMBDmnmupMDBqSofXjaLCB6OpV3TAKo/view?usp=sharing
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
77 | |
50 | |
46 | |
16 | |
12 |