Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
Hi,
PBI file attached.
Hope this helps.
Hi,
PBI file attached.
Hi,
Share some data to work with and show the expected result clearly.
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 |
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
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
Please help
Thanks
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.
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.
User | Count |
---|---|
94 | |
83 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
62 |