How to create a running total in Power BI DAX with 3 filter critera?

Becca 0
Viewed 29

I have this formula creating the cumulative 'Target Cost'.

Target Cost Actuals YTD = CALCULATE( SUM('Combined Usage'[TargetCost]), FILTER( ALLSELECTED('Combined Usage'[MonthNum]), ISONORAFTER('Combined Usage'[MonthNum], MAX('Combined Usage'[MonthNum]), DESC) ) )

I want to create the running cost as per AWS and as per Azure, and also per businessUnit, 1,2 & 3.

I'm aware I can add visualisation level filters perhaps for the business units, but I initially need to split the tegrt cost by AWS and Azure.

How do I add a filter into this DAX to also filter where Source = AWS?

Target Cost Actuals YTD = CALCULATE( SUM('Combined Usage'[TargetCost]), FILTER( ALLSELECTED('Combined Usage'[MonthNum]), ISONORAFTER('Combined Usage'[MonthNum], MAX('Combined Usage'[MonthNum]), DESC) ) )

Is there a way to then add in another filter that says AND business unit = "1"?

Thank you!

dax powerbi-desktop powerbi
1 Answers

you can use && in the filter

Target Cost Actuals YTD =
CALCULATE (
    SUM ( 'Combined Usage'[TargetCost] ),
    FILTER (
        ALLSELECTED ( 'Combined Usage'[MonthNum] ),
        ISONORAFTER (
                'Combined Usage'[MonthNum], MAX ( 'Combined Usage'[MonthNum] ), DESC
        )
            && 'Combined Usage'[Source] = "AWS"
            && 'Combined Usage'[business unit] = 1
    )
)
Umut K0
Related