Measure for cumulative value doesn't work unless column is in the visual

[gazpage]

Hi,

I have a data model with a fact table and a dimension table (SampleOrder). The Dimension Table looks like this:

Sample	Order
Top	1
Middle	2
Bottom	3
Other	4

<tbody>
</tbody>

The Sample Column is the relationship key back to the Fact table.

I want to create a simple table that shows the cumulative % of value represented by the Samples. The measure below works fine if I put the Order column in the visual, but if I drop it out and instead put the Sample column in then it just shows the actual percentage value and not the cumulative value. I can't really understand why this is happening.

I want to do a cumulative total of [Bank Value] (a simple SUM of the [Value] column

Bank Value Cum% (by sample) = 
DIVIDE ( 
 CALCULATE (
  [Bank Value],
  FILTER (
   ALL ( SampleOrder[Order] ),
   SampleOrder[Order] <= MAX ( SampleOrder[Order] )
  )
 ),
 CALCULATE (
  [Bank Value],
  ALLSELECTED()
 )
)

Thanks

 

[akice]

You don't change the formula when you change from 'Order' to 'Sample' on rows? Filters are column specific. Your formula only manipulates the filters on 'SampleOrder[Order]', specifically 'ALL SampleOrder[Order]' returns the all values in the column, but has no effect on the SampleOrder[Sample] column now on the rows.

 

[gazpage]

Ah, right. So even though there is a one to one correspondence between Order and Sample if Order is not in the visual it has no filters? So the All actually isn't doing anything.
I need to put Sample in the ALL.

Thanks!

 

[akice]

Yes. Tables are actually a grouping of independent columns and a filter on one has no direct impact on another until after the filter context is generated and applied to model. So if Orders is not in one (or more) of the rows/columns/filter area/slicer or explicitly added as a setfilter argument to CALCULATE, there is no filter on SampleOrder[Order] column; there is only a filter on SampleOrder[Sample] (except for grand totals of course).

 

[gazpage]

Really helpful. One more question if I may.

I often find myself wanting to do an average by day product category. In order to iterate over a list of products, rather than every row in a table, I would have done something like:

AVERAGEX(
    ALL ( Table[Product] ),
    [Measure]
)

I thought this would iterate over all of the unique values in the Product column. Do I now understand correctly that any filters on other columns remain in place? So if I want to ensure I am iterating every item then I probably need to put the ALL in a CALCULATETABLE with an ALL (Table) in the filter or something?

 

[akice]

Yes, other filters do remain in place and may impact the results. 'ALL ( Table[Product] )' does return a single column table with all the unique values of 'Product'; but the list is still cross filtered by other columns. If you had, say, Table[Product Color] selected in a slicer for just a few colors, Table[Product] values will be cross filtered down to the values that meet the Table[Product Color] choices and therefore AVERAGEX only iterates over the now smaller set of values which presumably will result in a different measure result. If you were to put both Product and 'Product Color' on rows the calculation really stands out. You'll see the groups of 'Product' averaged by 'Product Color' if you don't change your original measure (All products with same Color have same measure result).
Getting the results you want does depend on understanding filter context. In general for performance reasons you are better off iterating over a single column. But if you need to remove the filters on other columns you may need to do 'All ( Table )'. It depends on situation.