Apply an OR filter to multiple measures

[domtrump]

I am trying to figure out how to filter data in a power pivot report using an OR criteria on two different calculated measures without losing the data for the measure that doesn't meet the criteria. Let me try this simple example to illustrate.

I have sales data in this form

Year Type Amount
2014 Dolls 250
2014 Cars 300
2014 Balls 250
2015 Dolls 50
2015 Cars 0
2015 Balls 150
2014 Dolls 25
2014 Cars 300
2014 Balls 250
2015 Dolls 20
2015 Cars 100
2015 Balls 150

Using PP, I create two measures: one for 2014 and one for 2015. So I can get the following pivot table:

2014 2015
Balls 500 300
Cars 600 100
Dolls 275 70

I need to filter the data so that I get the records where 2014 sales >=300 OR 2015 sales >= 300

Back in the power pivot window, I created a calculated column that evaluates to 1 if either condition is met otherwise a 0. So far so good. When I filter the pivot table with a report filter set to 1, I want to get the Balls & Cars records since they meet my criteria. However...

I get this

2014 2015
Balls 500 300
Cars 600 0

when I want to get this

2014 2015
Balls 500 300
Cars 600 100

I understand what is happening, the filtering is occurring at the TABLE RECORD level when I want it to filter the records AFTER they have been built into the pivot table. How do I do and OR filter on the aggregated, pivoted data?

I tried to simplify my scenario as much as possible, but please let me know if additional information is required to come up with a solution.

Thank you.

 

[domtrump]

Any suggestions?

 

[Ozeroth]

Hi there,

This is how I've understood the output you want:
Show the Sales Amount for all Types of product with sales of 300+ in at least one year (in your example 2014 & 2015).

In the below I've assumed your table is named Sales.

My approach would be:

1. Define a Sales Amount Measure
   

   Sales Amount := SUM(Sales[Amount])

2. Define a measure to give you Sales Amount for only those Types with 300+ sales in at least one year in the pivot table:

Sales Amount for Types with Sales of 300 or more in at least one year :=
CALCULATE (
    [Sales Amount],
    FILTER (
        VALUES ( Sales[Type] ),
        COUNTROWS (
            FILTER ( ALLSELECTED ( Sales[Year] ), [Sales Amount] >= 300 )
        )
            > 0
    )
)

The 2nd measure filters Types down to those with Sales of 300+ in at least on year shown in the pivot table (ALLSELECTED). If you wanted to look at all years then you could use ALL(Sales[Year]) instead.

Also, you could parameterize the 300 rather than having a literal value in the measure.

 

[domtrump]

Ozeroth,

You are AWESOME! This has been bugging me all week. Perfect solution. Thanks so much.