Forum Discussion
Total is wrong when categorised is vendor wise
Dear All,
Measure used:
Dax measures for total sales is
CALCULATE(sum(table1(sales), DATESMTD(DIMDatesQ[EOM])). I tried using sumx but gives and error.
Issue: When I use only the month and the total measure, it shows correct, but as soon as I put vendor option in the matrix and I sum the total of all the vendors does not match the one displayed. The irony is the total is correct, but summation of all the total of the vendors is wrong please refer table below.
When i show like this it is correct.
| Month | Actual Total which is correct. Matrix shows the same, but refer pls another table below. Table A |
| Dec-25 | 5956 |
| Nov-25 | 5761 |
| Oct-25 | 6217 |
| Sep-25 | 5717 |
| Aug-25 | 6592 |
Now the issue is when i show like this in the matrix. Column highligted in Red is correct, but the
Table A
| Month | Distributor 1 | Distributor 2 | Distributor 3 | Distributor 4 | Distributor 5 | Total Displayed in matrix ( Which is correct) (Column H ) | Total of all the ones highlighted in red comes to mentioned below (Column I ) This is incorrect | Difference from Column H (actual) |
| Dec-25 | 1,684 | 137 | 2,313 | 1,436 | 79 | 5,956 | 5,649 (1684+137+2313+1436+79) | 307 |
| Nov-25 | 1610 | 140 | 2283 | 1327 | 96 | 5761 | 5,456 (1610+140+2283+1327+96) | 305 |
| Oct-25 | 1740 | 143 | 2461 | 1425 | 111 | 6217 | 5,880 (1740+143+2461+1425+111) | 337 |
| Sep-25 | 1567 | 128 | 2259 | 1339 | 121 | 5717 | 5,414 (1567+128+2259+1339+121) | 303 |
| Aug-25 | 1846 | 132 | 2527 | 1620 | 129 | 6592 | 6,254 (1846+132+2527+1620+129) | 338 |
- I have data which contains monthly sales values, in this you have the month the product name (unique) / the vendor code / and the sales amount. Table 1
- In another table I have the dimcodes where it bifurcates the product further into categories. Table 2
- In the third table I have the dimcodes for Type of vendors. Table 3
Table 1
| Month | Product | Vendor code | Sales |
| 31-12-2025 | product 1 | vendor 1 | 100 |
| 31-12-2025 | product 2 | vendor 2 | 200 |
| 31-12-2025 | product 3 | vendor 3 | 300 |
| 31-12-2025 | product 4 | vendor 4 | 400 |
| 31-12-2025 | product 1 | vendor 4 | 500 |
| 31-12-2025 | product 2 | vendor 3 | 600 |
| 31-12-2025 | product 3 | vendor 2 | 700 |
| 31-12-2025 | product 4 | vendor 1 | 800 |
| 31-12-2025 | product 4 | vendor 3 | 200 |
| 31-12-2025 | product 3 | vendor 1 | 250 |
| 31-12-2025 | product 2 | vendor 4 | 300 |
| 31-12-2025 | product 1 | vendor 2 | 600 |
Table 2
| category for products | Main Category |
| product 4 | Books |
| product 3 | Books |
| product 2 | Pens |
| product 1 | Pens |
Table 3
| category for Vendors | Main Category |
| vendor 3 | Cat 2 |
| vendor 1 | Cat 1 |
| vendor 4 | Cat 2 |
| vendor 2 | Cat 3 |
Dax measures for total sales is
CALCULATE(sum(table1(sales), DATESMTD(DIMDatesQ[EOM])). I tried using sumx but gives and error.
Requesting your assitance please
Warm Regards
Santosh
santoshlearner2 Please check which MonthDate-field are you using in the report.
One common mistake is to have a DateDimTable, but use the Date-field from a transaction table.
Best practice: Use the DateDimTable Field at the report
Alternate solution if you are using the Table1[Month] field at the report:Sales Amount MTD := VAR LastVisibleDate = MAX ( 'Table1'[Month] ) VAR CurrentYear = YEAR ( LastVisibleDate ) VAR CurrentMonth = MONTH ( LastVisibleDate ) VAR SetOfDatesMTD = FILTER ( ALL ( 'Table1'[Month] ), 'Table1'[Month] <= LastVisibleDate && YEAR ( 'Table1'[Month] ) = CurrentYear && MONTH ( 'Table1'[Month] ) = CurrentMonth ) VAR Result = CALCULATE ( SUM ( Table1[Sales] ), SetOfDatesYTD, KEEPFILTERS ( 'table 1'[Vendor code] ) ) RETURN Result
If this response was helpful in any way,
I’d gladly accept a kudo.
Please mark it as the correct solution.
It helps other community members find their way faster.Hi,
Thanks for everyone for assisting. I truly appreciate, But the response is not working. I cannot upload any file due to restrictions. Closing this, Thanks for every one.
13 Replies
Hi santoshlearner2,
Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to pcoley , krishnakanth240 , Ashish_Mathur and Rajavenkatesh for prompt and helpful responses.
Just following up to see if the Response provided by community members were helpful in addressing the issue. if the issue still persists please share the PBIX file to help further.
Best regards,
Prasanna KumarHello santoshlearner2
for MTD try with below DAX Code
MTD Sales =
SUMX (
VALUES ( Table1[Vendor code] ),
CALCULATE (
SUM ( Table1[Sales] ),
DATESMTD ( DimDatesQ[EOM] )
)
)
If my response helped you, please consider clicking
Accept as Solution ✅ and giving it a Like 👍 – it helps others in the community too.Your code is not working reason isDATESMTD() redefines the filter context
At Total level, Power BI recalculates the measure once
At Vendor level, it recalculates separately per vendor
Total ≠ Sum of vendors when time intelligence is involved
Thanks,
Connect with me on:
LinkedInHi,
I appreciate your quick reply, but getting the same error, am i doing something wrong.
Measure 1 : Total Sales = CALCULATE(SUMX(Table 1( Sales))
Measure 2: Total Sales MTD = CALCULATE([Total Sales],DATESMTD (DIMDatesQ[Date]))
Measure 3: Total Inflows by distributors= SUMX ( VALUES (table 1 ( vendor code), CALCULATE ([Total Sales MTD], DATESMTD ( DIMDatesQ[Date]))) )
Hi,
Share the download link of the PBI file.
This is a classic Power BI / DAX context issue, and your observation is 100% correct. Let me break it down clearly and then give you the exact fix.
Problem Summary (Why this is happening):
You are seeing this behavior:
Month level total -> Correct
Vendor-wise values -> Individually look fine
Sum of vendor rows ≠ Month total -> Mismatch
This means Power BI is not actually summing vendor rows to get the total.Instead, Power BI is re-calculating the measure at total level with a different filter context.
- This is NOT a data issue
- This is NOT a visual bug
- This is a DAX filter-context + relationship issue
Root Causes (There are 3 working together):
- Incorrect use of DATESMTD
- Dimension tables are filtering the same fact table
- Matrix totals are recalculated, not summed
The Correct & Safe Solution:
Step 1: Create a Base Measure (VERY IMPORTANT)
Total Sales = SUM (Table1[Sales])
Note: Never mix aggregation + time intelligence directly.Step 2: Correct MTD Measure (Vendor-safe)
MTD Sales = CALCULATE([Total Sales],DATESMTD (DIMDatesQ[Date]))
Note: Use Date column, NOT EOM column.Step 3: Force Correct Vendor Granularity (Fix Totals)
If totals still mismatch, use SUMX pattern:
---DAX---
MTD Sales (Correct)=
SUMX (
VALUES ( Table1[Vendor code] ),
CALCULATE (
[Total Sales],
DATESMTD ( DIMDatesQ[Date] )
)
)
---DAX---This is the KEY fix
- Now Power BI adds vendor-level values.
- Total = Sum of visible vendors
- Your Column H and Column I will match perfectly
=================================================================
Did I answer your question? Mark my post as a solution! This will help others on the forum!Appreciate your Kudos!!
Jaywant Thorat | MCT | Data Analytics Coach
LinkedIn: https://www.linkedin.com/in/jaywantthorat/
Join #MissionPowerBIBharat: https://shorturl.at/5ViW9
#MissionPowerBIBharat
LIVE with Jaywant ThoratDear Sir,
Amazing, thank you for such detailed reply, i did that but i am getting the same error. Am i doing something wrong. The measures which i created it as follows
Measure 1 : Total Sales = CALCULATE(SUMX(Table 1( Sales))
Measure 2: Total Sales MTD = CALCULATE([Total Sales],DATESMTD (DIMDatesQ[Date]))
Measure 3: Total Inflows by distributors= SUMX ( VALUES (table 1 ( vendor code), CALCULATE ([Total Sales MTD], DATESMTD ( DIMDatesQ[Date]))) )
Total Sales MTD =
VAR CurrentVendor = HASONEVALUE(Table3[Vendor Category])
RETURN
CALCULATE(
SUM(Table1[Sales]),
DATESMTD(DIMDatesQ[EOM]),
IF(CurrentVendor, Table3[Vendor Category], ALL(Table3))
)If this answer helped, please click 👍 or Accept as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pandeTry this
VAR IsSingleVendor =
HASONEVALUE(Table3[Vendor Category])
RETURN
CALCULATE(
SUM(Table1[Sales]),
DATESMTD(DIMDatesQ[Date]),
IF(
IsSingleVendor,
VALUES(Table3[Vendor Category]),
ALL(Table3[Vendor Category])
)
)Please share pbix file over gdrive and share the link
Hi @santoshlearner2,
Just following up to see if the Response provided by community members were helpful in addressing the issue. if the issue still persists please share the PBIX file to help further.
Best regards,
Prasanna KumarHi,
Thanks for everyone for assisting. I truly appreciate, But the response is not working. I cannot upload any file due to restrictions. Closing this, Thanks for every one.
