I need to count the users whose average sale has been greater than or equal to 50 in past 3 months

[735Alka]

Dear Community Users ,

I have sales data which having columns date,user code,approval status,sales quantity. final sales quantity will be considered when approval status="Approved"

I need to count the users whose average sale has been greater than or equal to 50 in the past three months

For an example, if a user sold in 3 months, the average will be the sum of sales in 3 months divided by 3, and so on for 2 and 1 months. 

Please help me with this

Thanks

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result clearly.

[735Alka]

Hi, here is my sample data 

Status	Date	User Code	Sales Quantity
Approved	27-02-2024	43131	50
Approved	21-03-2024	43131	50
Approved	02-02-2024	43130	50
Approved	27-03-2024	43130	60
Approved	27-03-2024	43130	60
Approved	01-01-2024	43130	1
Approved	18-03-2024	43130	8
Approved	09-01-2024	43131	150
Approved	02-02-2024	43130	150
Approved	27-01-2024	43130	72
NA	12-02-2024	43131	180
NA	07-01-2024	43131	98
Approved	05-03-2024	43130	70
Approved	27-02-2024	43130	90
Approved	06-02-2024	43130	50
NA	06-12-2023	70313	100
Rejected	21-02-2024	43130	30
NA	07-01-2024	43131	100
NA	12-02-2024	43131	170
Approved	09-03-2024	33313	201
Approved	26-02-2024	33313	200
Approved	09-01-2024	43131	50
Approved	21-03-2024	43131	100
Approved	04-12-2023	43130	12
Approved	18-03-2024	43130	130
Approved	01-01-2024	43130	70
Approved	17-01-2024	43130	80
Approved	22-12-2023	43130	40
Approved	08-12-2023	43130	50
Approved	23-03-2024	43130	80
Approved	19-02-2024	43130	170
Approved	14-02-2024	43130	120
Approved	23-03-2024	43130	80
Approved	14-02-2024	43130	120
Approved	04-12-2023	43130	180
Approved	05-02-2024	43130	100
Approved	07-02-2024	33313	65
Approved	10-01-2024	43130	180
Approved	19-12-2023	43130	110
Approved	25-01-2024	33313	185
Rejected	09-12-2023	33313	60
NA	17-02-2024	43131	180
NA	31-03-2024	70313	100
NA	31-03-2024	70313	100
NA	30-03-2024	70313	100
NA	28-03-2024	70313	100
NA	24-03-2024	70313	85
NA	16-03-2024	70313	80
NA	15-03-2024	70313	100
NA	14-03-2024	70313	75
NA	06-01-2024	33313	150
NA	02-02-2024	33313	130
NA	18-03-2024	33313	135
NA	02-02-2024	33313	125
NA	07-01-2024	43131	125
Approved	11-03-2024	43131	51
Approved	19-12-2023	43131	24
Approved	19-12-2023	43131	19
Approved	11-01-2024	43131	50
Approved	11-03-2024	43131	150
Approved	14-02-2024	43131	30
Approved	28-12-2023	43131	80
Approved	12-02-2024	43131	190
Approved	31-12-2023	70313	25
Approved	30-03-2024	70313	20
Approved	31-01-2024	70313	20
Approved	04-12-2023	43130	80
Approved	06-12-2023	43130	5
Approved	01-01-2024	43130	80
Approved	17-01-2024	43130	40
Approved	31-01-2024	43130	200
Approved	09-03-2024	43130	200
Approved	18-03-2024	43130	51
Approved	11-12-2023	43130	150
Approved	11-12-2023	43130	200
Approved	11-12-2023	43130	200
Approved	11-12-2023	43130	200
Approved	24-01-2024	33313	200
Approved	24-01-2024	33313	26
Approved	24-12-2023	33313	200

and my output number will be 3 which has average greater than or equal to 50

user code	Jan	Feb	march	average	count
33313	411	265	201	292	1
43130	723	850	739	771	1
43131	250	270	351	290	1
70313	20		20	20	0

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[735Alka]

Hi @Ashish_Mathur 
Here , The condition I have is slightly different; I need to determine the number of users and their volume  whose last 3 month average sale greater than or equal to 50.
for which i have created some measure for the count of users and their volume as well
Now, the measure for the no. of users is giving correct value but their volume is not correct value and i need volume is CARD Visual
i have attached a link in which i have attached file and excel working sheet which is my final users and their volume 
 Thanks

https://drive.google.com/file/d/1w8cA2yDBiJEGMKJIYbgRUKIbam-WFviy/view?usp=drive_link 

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

[735Alka]

Hii @Ashish_Mathur 
Thanks ,the solution you provided is working fine, but now I need to find the average volume of those customers whose sale is >=50, for which I am using this dax, but it is not giving me the correct value
volume=calculate(Average monthly sale for 3 months ended,
filter(
summarize(data,data[user code],[Measure]))
Here ,Measure is the no. of users whose sale is>=50
and when using this dax ,it is skipping the volume of users which sale is not in current selected month while have sale in last 2 months

L3M AVG>=50 VOLUME = CALCULATE(Average monthly sale for 3 months ended ,

    FILTER(

    SUMMARIZE(data,data[User Code],"sum qty",[Flag]),[Flag]=1))

Please help 
Thanks

[Ashish_Mathur]

That figure is already present in the file which i shared with you earlier - 1336.67.  If that is not the figure you want, then show the expected result  with your Excel workings.

[v-kaiyue-msft]

Hi @735Alka ,

Based on your description, I created this data.

1. Create a measure to obtain the user's average sales.

Measure =
VAR _min_date = CALCULATE(MIN('Table'[date]),FILTER(ALL('Table'),'Table'[user code] = MAX('Table'[user code])))
VAR _max_date = CALCULATE(MAX('Table'[date]),FILTER(ALL('Table'),'Table'[user code] = MAX('Table'[user code])))
VAR _diff = DATEDIFF(_min_date,_max_date,MONTH) + 1
VAR _sum = CALCULATE(SUM('Table'[sales quantity]),FILTER(ALL('Table'),'Table'[user code] = MAX('Table'[user code]) && 'Table'[approval status ] = "Approved"))
VAR _result = DIVIDE(_sum,_diff)
RETURN
_result

2. Create a measure and mark results greater than 50.

Measure 2 =
IF('Table'[Measure] >= 50,1,0)

3. Use measure 2 as a filter for visual objects, and the results obtained are as shown in the figure below.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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