DAX: Running Total in Pivot Table (Works until filtered)

[rlansing]

Hello,

This is my first time asking a question. I am trying to model my data, which is approximately 7 million rows of product movement data. Included in the dataset are references to Shipment Date, Warehouse Number, Store Number, Product information (UPC), and the quantity of cases shipped.

I need help on multiple measures (I am reading Building Data Models with PowerPivot 2015 edition). For this problem I have a Matrix table showing Product description, Qty, Year Previous Qty, the % change between the two, a Product Ranking.

What I am trying to build is a running total for each item. So basically the "Cumulative Qty" measure would show the cumulative total of the row and any that ranked higher. This will allow us to build a cumulative share measure that will allow us to focus on the top X % of the products produced by a manufacturer. I hope that all makes sense. here is the measure:

Cumulative Qty = IF ( HASONEVALUE( UniData[Desc] ) && [TotQty] > 0, SUMX ( TOPN ( [Qty Rank], CALCULATETABLE ( VALUES ( UniData[Desc] ), All( UniData[Desc] ) ), [TotQty] ), [TotQty] ), BLANK() )

This works prior to a date filter being applied. Once the data filter hits (0-4 weeks, 0-13 weeks, etc...) all of the numbers go to 100% or 7880 in the case I am testing on for all products.

Is there a way I can change my measure to allow for additional filters, but only base this running total on whats visible?

Thanks in advance for your patience!

[edwardrmiles]

@rlansing @Sean also check for bi-directional relationships.  I had the same problem and it was caused by a bi-directional relationship, so ALLSELECTED didn't help.  These are the default relationship type (not ideal IMO) and you can change it to a single direction in the relationship view

[rlansing]

Any thoughts on this question?

[Sean · Accepted solution]

I've always had this issue with my Running Total formula - works only when there are NO other filters

I should say - after you've set up the report - you can't apply additional filters 

I've solved this by creating a separate tab for the Running Totals reports where no filters/slicers exist that let you modify the data.

We should be lucky enough that Marco Russo sees this post and answers us (I use the formula from his book)

How many tables do you have in your data model?

I bet your filter comes from a different table than where the measure is - because sometimes if the filter is from the same table the formula works fine? So when you apply the filter from a different table it doesn't filter the table with the measure the way you want it too and consequently produces the wrong result.

[rlansing]

Thank you for the reply! I think you hit the nail on the head. I will review how I am filtering the data and try different combinations. It's good to hear that I am not the only one with this problem.

Thanks again!

[LanceDelano]

Have you tried using AllSelected rather than All?

[Anonymous]

@LanceDelano That ALLSELECTED suggestion just got my running total fixed to obey my Report Level Filters. Thanks!

[rlansing]

Here is an example of the data with the Cumulative measures showing on the far right.

Product names have been hidden, but if you need more info please ask...

[rlansing]

Yes, but it doesn't change the result. Still 100% for all items...