CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
Rebecca
Rebecca

Reputation: 67

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

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 target 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!

Here is some demo data:

enter image description here

I would like my calculation to give me a running total where source = AWS and Business unit = 1.

So in monthnum 1 the total = 4.50.

In monthnum 2 the total would be the previous months 4.50 + this months 3.00 = 7.50

In month num 3 the total would be 7.50 + 0.00 = 7.50

Thanks

Upvotes: 0

Views: 1194

Answers (1)

Umut K
Umut K

Reputation: 1388

you can use && in the filter

    Target Cost Actuals YTD =
    CALCULATE (
    SUM ( 'Table'[Target Cost] ),
    'Table'[Business Unit] = 1
        && 'Table'[Source] = "AWS",
    FILTER (
        ALLSELECTED ( 'Table'[Monthnum] ),
        ISONORAFTER ( 'Table'[Monthnum], MAX ( 'Table'[Monthnum] ), DESC )
    )
)

this is the sample file if you need

result

Upvotes: 1

Related Questions