Matrix aggregating totals

[MagikJukas]

Hello,

I want to aggregate values at higher level: the formula below works fine, except for the red part where I am trying to aggregate the values:

PD proj test = 

VAR tab=CALCULATE([Qty x Price],FILTER(ALLSELECTED('Date list'[Date]),'Date list'[Date]<=MAX('Date list'[Date])))+0

VAR tab2=IF(tab<0,tab)

VAR tab3=ADDCOLUMNS(CROSSJOIN(VALUES(Data[Material]),VALUES('Date list'[Date])),"tot",tab2)

RETURN IF(ISINSCOPE('Date list'[Date]),IF(ISINSCOPE(Data[Material]),tab2,CALCULATE(SUMX(tab3,[tot]),VALUES('Date list'[Date]))))

As you can see from this snapshot, the total is not matching:

I know it is a common problem, I have been reading some posts, but I am not making much progress.

thank you

[amitchandak]

@MagikJukas , This formula is enough if you are using date list table in visual and slicer and it marked as date table

CALCULATE([Qty x Price],FILTER(ALLSELECTED('Date list'[Date]),'Date list'[Date]<=MAX('Date list'[Date])))+0

assuming [Qty x Price] is a measure and you are creating a new measure

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

You can also explore window function

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

[MagikJukas]

unfortunately, the formula does not aggregate correctly. I need to show the negative values at the lowest level, and aggregate the latter at subtotal level.