Forum Discussion

jalaomar's avatar
jalaomar
Icon for Helper IV rankHelper IV
1 year ago
Solved

Issue with data wrong on row level when no filter applied but correct when applying filter

Dear all,   I am stuck with an issue with my data and not sure how to proceed. I have a table with all projects and in this table we capture monthly snapshots related to sales , budget. I have cre...
Need Help
  • Akash_Varuna's avatar
    Akash_Varuna
    1 year ago

    Hi jalaomar It might be because SUMX aggregates across rows without proper filter context. Modify your measure to explicitly filter by the current date for example like CALCULATE([Sales Price], TABLE[Date] = MAX(TABLE[Date])). Ensure the Date field is included in the visualization or model filters. 

rohit1991's avatar
rohit1991
Icon for Super User rankSuper User
1 year ago

Hi jalaomar

 

The issue comes from the way your measure is written:

SALES PRICE = SUMX( VALUES(Table[Project]), [Sales Price] )

Here [Sales Price] doesn’t respect the date filter, so when a month has no data (e.g. May), Power BI still pulls values from earlier months, which makes totals look wrong unless you apply a project/date filter.

 

Could you please try below steps:

Make sure your measure is constrained by both Project and Date. For example:

Sales Price =
CALCULATE(
    SUM(Table[Sales_Price]),
    KEEPFILTERS( VALUES('Date'[Date]) )
)

 

If your Date table isn’t directly related to the snapshot column, use TREATAS:

Sales Price =
CALCULATE(
    SUM(Table[Sales_Price]),
    TREATAS( VALUES('Date'[Date]), Table[SnapshotDate] )
)

 

Also check your visual: turn off “Show items with no data” if you don’t want blank months to display.