User Profile
Alka735
Frequent Visitor
Joined 3 years ago
User Widgets
Contributions
Re: calculating current month sales for only those dealers on which activity performed in L3M
I am trying with this logic, but not getting correct results,some customers sales are skipped while calculation L3M Active Dealers Current Month Sales = VAR L3M_Dealer_List = CALCULATETABLE( VALUES(DATA1[Dealer ERP Code]), -- Last 3 completed months DATESINPERIOD( 'Calendar'[Date], EOMONTH(MAX('Calendar'[Date]), -1), -3, MONTH ), -- Activity condition DATA1[Activity Conducted At] = "dealer Counter", -- Valid dealer NOT(ISBLANK(DATA1[Dealer ERP Code])) ) RETURN CALCULATE( [TOTAL_SALE], -- Apply only those dealers TREATAS(L3M_Dealer_List, DATA1[Dealer ERP Code]) )865Views1like1Commentcalculating current month sales for only those dealers on which activity performed in L3M
Hii All, Hope you are doing well. I need help with a requirement: we want to calculate current-month (or month-wise) sales only for those customers who had activity at their counter in the last 3 completed months. For example, if April is the current month, only customers with activity between January and March should be considered, and their April sales should be included regardless of whether they had activity in April or not. Please help me with this question. I am sharing the sample dataset for 2 tables ,Activity and Sales,we need to consider the customers where activity conducted at "Dealer Counter" and the final output will be 700(total sales) Date Customer ID Sales Amount 2026-04-02 C001 100 2026-04-10 C002 200 2026-04-12 C003 150 2026-04-15 C004 300 2026-04-18 C005 250 2026-04-20 C006 400 Date Customer ID Activity Conducted At 2026-01-10 C001 dealer Counter 2026-02-15 C002 dealer Counter 2026-03-05 C001 dealer Counter 2026-03-20 C003 dealer Counter 2026-01-25 C004 other 2026-02-10 C005 dealer Counter 2026-04-05 C006 dealer Counter Customer ID April Sales C001 100 C002 200 C003 150 C005 250Solved868Views1like6CommentsRe: No. of Dealers Based on % Sales via Users with Sales ≥ 50 Bags
v-veshwara-msft I need these calculations in a DAX measure, as I need to perform them on a month-wise basis using a calendar table. Due to the large size of the dataset, the calculated table approach previously provided is resulting in a "not enough memory available" error. Therefore, could you please provide a DAX measure-based solution instead?849Views0likes0CommentsRe: No. of Dealers Based on % Sales via Users with Sales ≥ 50 Bags
and the final ouput is : and formula to calculate range is IF(D2=0,"0%",IF(D2<=0.1,"1-10%",IF(D2<=0.2,"11-20%",IF(D2<=0.35,"21-35%",">35%")))) No. of Dealers Range 3 0% 2 >35% 1 1-10% 3 21-35% and no. of users No. of users range 41 >35% 15 0% 2 1-10% 18 21-35%1KViews0likes1CommentRe: No. of Dealers Based on % Sales via Users with Sales ≥ 50 Bags
and table 3 data dealer code User Code Sales DATE Total D5 131637 29-04-2025 80 D5 135330 30-04-2025 40 D5 145500 29-04-2025 80 D2 124050 29-04-2025 87 D2 166311 28-04-2025 89 D2 22394 28-04-2025 150 D2 28067 28-04-2025 85 D2 78536 28-04-2025 86 D3 110798 28-04-2025 30 D3 110868 29-04-2025 15 D3 112281 12-04-2025 49 D3 115558 29-04-2025 81 D3 131166 29-04-2025 82 D3 135147 29-04-2025 200 D3 144726 15-04-2025 100 D3 162432 28-04-2025 85 D3 166283 28-04-2025 82 D3 166311 28-04-2025 150 D3 166516 28-04-2025 87 D3 172429 10-04-2025 100 D3 172429 25-04-2025 100 D3 181813 28-04-2025 10 D3 184910 21-04-2025 100 D3 188472 28-04-2025 89 D3 188525 28-04-2025 84 D3 189148 28-04-2025 47 D3 189592 28-04-2025 280 D3 189842 28-04-2025 275 D3 189843 29-04-2025 83 D3 22403 28-04-2025 95 D3 22405 28-04-2025 45 D3 22485 28-04-2025 89 D3 22614 28-04-2025 590 D3 44378 29-04-2025 86 D3 44384 28-04-2025 275 D3 50472 28-04-2025 81 D3 50564 15-04-2025 35 D3 53495 16-04-2025 100 D3 54225 29-04-2025 60 D3 62792 28-04-2025 81 D3 66443 28-04-2025 175 D3 84328 20-04-2025 100 D3 85002 29-04-2025 89 D4 135379 17-04-2025 150 D4 144731 19-04-2025 140 D4 145050 28-04-2025 165 D4 156579 02-04-2025 300 D4 186778 30-04-2025 108 D4 22609 07-04-2025 150 D4 28038 30-04-2025 53 D4 28040 30-04-2025 52 D4 28041 30-04-2025 55 D4 28043 30-04-2025 25 D4 28044 30-04-2025 61 D4 28046 30-04-2025 60 D4 28047 30-04-2025 53 D4 28048 30-04-2025 57 D4 53495 22-04-2025 100 D7 144899 24-04-2025 150 D7 184858 07-04-2025 100 D7 25599 07-04-2025 250 D7 25622 30-04-2025 250 D7 73595 21-04-2025 80 D8 103543 01-04-2025 50 D8 111318 01-04-2025 50 D8 125666 01-04-2025 50 D8 190304 10-04-2025 45 D8 27020 01-04-2025 50 D8 27217 01-04-2025 50 D8 27310 01-04-2025 50 D8 27505 01-04-2025 50 D8 27521 01-04-2025 50 D8 51084 10-04-2025 200 D8 51102 30-04-2025 100 D8 51144 30-04-2025 150 D8 51192 30-04-2025 90 D8 57940 30-04-2025 200 D8 81643 22-04-2025 200 D8 93356 01-04-2025 50 D9 177497 06-04-2025 350 D9 5925 30-04-2025 53 D9 6106 01-04-2025 190 D9 6136 30-04-2025 51 D9 6260 09-04-2025 30 D9 6293 30-04-2025 51 D9 6364 06-04-2025 190 D9 6427 30-04-2025 55 D9 92538 30-04-2025 52 and my final output is for no. of dealers in ranges Dealer Code Dealer sale from table 2 dealer sale from table3 with users sale>=50bags % sale Range D1 2500 0 0 0% D2 2000 497 0.2485 21-35% D3 6034 3799 0.629599 >35% D4 2676 1504 0.562033 >35% D5 2600 160 0.061538 1-10% D6 0 0 0 0% D7 3010 830 0.275748 21-35% D8 0 1390 0 0% D9 3120 992 0.317949 21-35%1KViews0likes2CommentsRe: No. of Dealers Based on % Sales via Users with Sales ≥ 50 Bags
Hi Ashish_Excel here is my sample data for all table and my final expected output. Please consider it. table 1-data Dealer Code D1 D2 D3 D4 D5 D6 D7 D8 D9 and table 2 -data date Dealer Code Dealers sale 25-04-2025 D14 700 25-04-2025 D17 700 30-04-2025 D12 700 24-04-2025 D18 700 25-04-2025 D10 700 24-04-2025 D11 700 26-04-2025 D17 700 30-04-2025 D16 700 19-04-2025 D15 700 25-04-2025 D13 700 24-04-2025 D15 700 09-04-2025 D4 626 19-04-2025 D3 1140 10-04-2025 D1 1050 18-04-2025 D7 770 19-04-2025 D4 190 30-04-2025 D3 1414 30-04-2025 D9 3120 17-04-2025 D4 860 05-04-2025 D5 1100 05-04-2025 D2 680 30-04-2025 D1 270 23-04-2025 D7 820 25-04-2025 D3 1020 28-04-2025 D7 720 05-04-2025 D3 420 25-04-2025 D1 900 15-04-2025 D3 800 28-04-2025 D1 120 05-04-2025 D7 700 19-04-2025 D1 160 08-04-2025 D3 640 19-04-2025 D2 300 25-04-2025 D2 300 11-04-2025 D4 300 10-04-2025 D2 520 10-04-2025 D4 100 24-04-2025 D4 600 11-04-2025 D3 600 30-04-2025 D5 500 26-04-2025 D5 500 24-04-2025 D5 500 15-04-2025 D2 2001KViews0likes0CommentsNo. of Dealers Based on % Sales via Users with Sales ≥ 50 Bags
Hi all, I have three tables: Table1: Unique list of Dealer Codes with basic details. Table2: Dealer-wise total sales (in bags) with month and date columns. Table3: Dealer Code, User Code, User Sales (in bags), and Date. Each dealer can be mapped to multiple users. Requirement: I need to categorize dealers (from Table1) into percentage ranges based on how much of their total sales (from Table2) come from users (from Table3) who sold ≥ 50 bags. The ranges are: 1–10% 11–20% 21–35% >35% Example: If a dealer has 400 bags of total sales (Table2), and the sum of sales from users (Table3) with individual sales ≥ 50 bags is 150, then: 150 ÷ 400 = 37.5% → This dealer falls into the >35% category. I want to count how many dealers fall into each of these ranges and also i have need the user count based on dealer range as For each user, calculate their contribution % toward their mapped dealer’s monthly total sales. Only include users whose own sale is ≥ 50 bags. Based on this % contribution, categorize users into these bands: 1–10% 11–20% 21–35% >35% Count the number of users falling into each of these bands. Example For Dealer D001: Dealer Sale (Table2) = 400 User Sales (Table3): A = 15 (ignored, <50) B = 20 (ignored, <50) C = 50 D = 55 So we calculate % as: C → 50 / 400 = 12.5% → falls in 11–20% D → 55 / 400 = 13.75% → falls in 11–20% Final Result: → 2 users in 11–20% band. Please help me to get the solutions.Solved1.1KViews0likes8CommentsRe: Count of Users with checked condition 1 and zero
hi, thanks for the solution but i am sharing measures on which i working. I want to count the flag measure which has value 1 based on my condition but it is giving me blank value or incorrect value and need to work it for all the slicers. here are measures---- Last Month Users Volume = CALCULATE(SUM('Table'[Sale]),DATEADD(Calender[Date],-1,MONTH)) This Month Users Volume = CALCULATE(SUM('Table'[Sale]),DATEADD(Calender[Date],0,MONTH)) Flag Measure = IF([Last Month Users Volume]<>0 && [This Month Users Volume]=BLANK(),1,0) User_Count = SUMX( VALUES('Table'[Users]), IF([Flag Measure]=1,1,0 ) ) Thanks Date Users Sale 30-01-2023 26406 20 13-03-2023 5463 20 30-01-2023 5463 20 18-04-2023 2914 25 18-04-2023 2892 35 16-04-2023 13421 201 18-01-2023 555 60 30-04-2023 18543 205 28-02-2023 23190 10 01-04-2023 26515 60 16-03-2023 678 1 15-03-2023 1350 150 04-04-2023 1324 330 05-04-2023 9876 10 07-01-2023 234 46 06-03-2023 1345 44 18-04-2023 777 250 24-02-2023 35218 102 07-02-2023 234 88 04-01-2023 234 30 29-03-2023 21465 300 26-03-2023 21465 200 10-01-2023 19879 10 26-04-2023 1345 45 11-04-2023 1345 45 03-04-2023 1345 28 28-03-2023 1345 32 24-03-2023 1345 35 18-03-2023 1345 35 18-01-2023 28141 150 28-02-2023 28226 150 31-01-2023 33681 250 28-01-2023 15175 40 19-04-2023 30196 30 28-04-2023 25574 35 24-03-2023 901 300 01-03-2023 20535 30 01-03-2023 20535 30 28-02-2023 30245 60 25-03-2023 34573 10 11-02-2023 2450 30 28-04-2023 35533 60 18-01-2023 30067 30 03-04-2023 2078 35 16-01-2023 16234 200 30-04-2023 1234 10 30-04-2023 1345 10 01-03-2023 1066 30 17-04-2023 25605 150 26-04-2023 10664 1 27-03-2023 5525 1 13-04-2023 34630 85 13-01-2023 16599 200 14-03-2023 16208 27 24-03-2023 8470 130 19-01-2023 28768 90 25-03-2023 14031 10 25-03-2023 14291 30 16-03-2023 14402 25 16-03-2023 14336 25 10-04-2023 30173 400 20-04-2023 345 45 16-02-2023 30467 45 06-02-2023 31544 5 16-02-2023 4949 60 16-02-2023 4949 60 24-04-2023 5153 40 21-04-2023 234 35 22-03-2023 345 10 05-03-2023 345 30 13-02-2023 8926 2 06-01-2023 1380 175 30-04-2023 901 200 30-04-2023 901 200 08-02-2023 13810 2 09-01-2023 765 30 10-03-2023 28313 5 04-02-2023 1358 30 24-02-2023 455 12 23-03-2023 193 12 10-03-2023 26437 200 25-03-2023 456 30 19-02-2023 6720 62 06-04-2023 2833 10 13-01-2023 3116 30 26-03-2023 12369 60 31-03-2023 12558 10 17-03-2023 12802 10 27-03-2023 3617 80 01-02-2023 1345 45 12-02-2023 1345 35 02-03-2023 1345 54 12-03-2023 1345 52 17-04-2023 1345 35 28-02-2023 1345 35 07-02-2023 1345 38 04-02-2023 456666 55 05-03-2023 500 120 12-02-2023 27267 25 18-01-2023 27267 251.4KViews0likes0Comments
Data Privacy
Microsoft Fabric Community and Privacy
To learn more about how we manage your data, please review the Microsoft Fabric Community Data Privacy guide.