Forum Discussion

MagikJukas's avatar
MagikJukas
Icon for Resolver III rankResolver III
3 years ago

Matrix aggregating totals

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 Pric...
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
3 years ago

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's avatar
    MagikJukas
    Icon for Resolver III rankResolver III
    3 years ago

    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.