Forum Discussion

santoshlearner2's avatar
santoshlearner2
Icon for Resolver II rankResolver II
7 months ago
Solved

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 t...
Need Help
  • pcoley's avatar
    pcoley
    7 months ago

    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.

  • santoshlearner2's avatar
    santoshlearner2
    7 months ago

    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.

Jaywant-Thorat's avatar
Jaywant-Thorat
Icon for Super User rankSuper User
7 months ago

Hi santoshlearner2 

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):

  1. Incorrect use of DATESMTD
  2. Dimension tables are filtering the same fact table
  3. 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 Thorat

santoshlearner2's avatar
santoshlearner2
Icon for Resolver II rankResolver II
7 months ago

Dear 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]))) )