Running Total Breaks when Adding Top N

[Sburi2]

Hello!

I'm trying to obtain obtain a running total. I have the DAX working:

Running Total = 

VAR thisCategory = [sum Total Monthly Cost]

VAR RunningTotal_ThisCategory = 
    CALCULATE(
        [sum Total Monthly Cost],
        FILTER(ALLSELECTED(tbApptioAppCosts[App Name]), [sum Total Monthly Cost]>=thisCategory)
    )

RETURN
RunningTotal_ThisCategory

However, when I filter the visual to Top 10 by App Name, the running totals fails to be cumulative.

Would really appreciate help understanding what is happening under-the-hood so I can resolve issues like this in the future myself.

This is what I want

Correct Expectation	Total	Cumulative Total
Cat A	300	300
Cat B	200	500
Cat C	100	600

This is what I'm getting

What's Happening	Total	Cumulative Total
Cat A	300	300
Cat B	200	200
Cat C	100	100

Thank you!

[Fowmy · Accepted solution]

@Sburi2 

I made a sample file. as you can see, I filtered by top 3, and still, the result is correct. In your initial posting of the question, you had used ALLSELECTED when you calculated the all appname total, you should use ALL. check my formula below: I attached the file as well.

Cummulative = 
var __current = [Total Sales]
var __all = CALCULATE( [Total Sales] , ALLSELECTED(financials[Product]))
var __cumm = 

    CALCULATE(
        [Total Sales],
        FILTER(
            ALLSELECTED(financials[Product]),
            [Total Sales] >= __current
        )
    )
return
DIVIDE( __cumm , __all )

[Fowmy]

@Sburi2 

Your formula should work, can you re-recheck ?

[Sburi2]

Thanks @Fowmy , good to know I'm not a complete dunce. It *does* work as long as I don't have Top N filter applied on [App Name] by [Total Monthly Cost]. Once I apply the filter however, it no longer calculates the running total.

[Fowmy · Accepted solution]

@Sburi2 

I made a sample file. as you can see, I filtered by top 3, and still, the result is correct. In your initial posting of the question, you had used ALLSELECTED when you calculated the all appname total, you should use ALL. check my formula below: I attached the file as well.

Cummulative = 
var __current = [Total Sales]
var __all = CALCULATE( [Total Sales] , ALLSELECTED(financials[Product]))
var __cumm = 

    CALCULATE(
        [Total Sales],
        FILTER(
            ALLSELECTED(financials[Product]),
            [Total Sales] >= __current
        )
    )
return
DIVIDE( __cumm , __all )

[Sburi2]

That's so odd... I open up your file and it works. I open up my file and it doesn't. I compare the DAX formulas and they are functionally the same. I'm not sure where I'm going wrong.

I also just noticed that it actually is performing a cumulative count, but only when the previous numbers exactly equal the current numbers. I'm not using =, I'm using >=... not sure what's wrong. 

[Fowmy]

@Sburi2 

I am not sure how your model is setup. 

Share your dax formula to check. 

you can also attach a sample file like I did, save it in OneDrive or any other cloud location and share the link

[Sburi2]

Same formula as before essentially. Would share the file but the data is confidential of course and if I simplify it I'll end up with a (less clean) version of your file 🙂

I did find out what was causing the issue but not why. I have another filter on the page on the column with the monthly cost. I'm filtering out things that are = 0. For some reason, having this filter applied breaks the cumulative function.

[Fowmy]

@Sburi2 

Hope it works fine now?

[Sburi2]

Indeed, thanks! Just wish I knew what was wrong!

[Fowmy · Accepted solution]

@Sburi2 

When you posted the question initially, I noticed that you had used ALL instead of ALLSELECTED. It was one reason for the incorrect results when you applied TOPn filter. Apart from that you also had applied some other filters. 

ar __all = CALCULATE( [Total Sales] , ALLSELECTED(financials[Product]))