Total not correct for measure showing only positive values

[Anonymous]

Hi there Power Bi community!

I'm stuck with a measure that is not showing the right value in subtotal and total rows. Here's what I've done:

I have two tables which are date related. Table 1 has a whole number field called Amount and Table 2 has a whole number field called Projections.

I created two measures that simply sum the values of these two fields and are called Total Amount and Total Projections.

I need to create a measure that shows the difference between both measures (Total Projections - Total Amount) but with two conditions:

1. It should only show values after Today's date.
2. It should only show positive values. 

I created a measure that almost fits, first I performed the arithmetic subtraction, showing positive values and putting Blank() in negative ones:

Difference = 
IF(
    [Total Amount] > SOW_PROJECTIONS[Total Projections],
    BLANK(),
    SOW_PROJECTIONS[Total Projections] - [Total Amount]
)

Then I filtered above measure to show just the values after Today's date:

Next Diff = 
CALCULATE(
    [Difference],
    FILTER('CALENDAR',
    'CALENDAR'[Date] > TODAY()
    )
)

But when I show Next Diff in a Matrix visualization the subtotal and total rows don't show the correct sum of the rows above. For 2020 total it should show 310,500, but it's showing 140,050

What I've found out is that negative Difference values are still adding in the Total rows (negative values from April 2020 until September 2020), I guess it's due to this is a measure and it evaluates the total with its own context.

How can I work this around?

I really appreciate any help you can provide and thanks in advance to all of you who interact in this awesome community 🙂

Sebastián.

[amitchandak · Accepted solution]

Try something like this

sumx(summarize(Table,Table[Year],Table[Month],"_diff",[Next Diff]),[_diff])

[Anonymous]

Thanks a lot @amitchandak it did the trick! I'd read about using those functions, but until now I haven't understood how to use them.