filtering using calculated fields

[MNGoodyear]

I have sales order data by account/year/commodity/rep /order reference etc... 

I want to create an interactive enquiry where the user can ask quesitons of the data. For example

Using the below basic table, show me all accounts where sales of category B are 0/Null in 2018 but sales of category A are > 0 in 2018, in this example i would expect to return account 1 and account 2

Account	Category	Year	Qty
1	A	2017	100
1	B	2017	50
1	A	2018	150
2	A	2018	75
3	A	2018	10
3	B	2018	50

I have created measures to summarise the qty by category  for each year using filter parematers such as :

Sales Cat A 18 = CALCULATE(sum('Report 1'[Qty]),FILTER('Report 1',[Category] = "A"),FILTER('Report 1','Report 1'[Year]=2018))

But i cannot work out how to apply the filtering 

Help

Thanks

[AlB]

Hi @MNGoodyear

How about creating a table

Table= FILTER(ALL(Report1[Account]), [Sales A] > 0 && [Sales B] =0)

with

[Sales A]=CALCULATE(SUM(Report1[Qty]), Report1[Category]="A"))

[Sales B]=CALCULATE(SUM(Report1[Qty]), Report1[Category]="B"))

You can set the year on a slicer

[MNGoodyear]

hi the objective is to categorise a number of scenarios, for example adding sliders for :

Category A 2017

Category B 2017

Category A 2018

Category B 2018

so the user can interactively apply different types of filters to the data which i dont think the solution will allow

[AlB · Accepted solution]

@MNGoodyear

If I understand correctly you want a sort of dynamic measure, with dynamic code, that calculates different things depending on what is selected on slicers.

I would suggest using harvester measures. You create a separate, unconnected  table for each of the variables you want to play with, like Category, Year, and if you need it, Type of comparison (>0, =0)

With the harvester measure you capture what's been selected and 

[Sales1]=CALCULATE(SUM(Report1[Qty]), Report1[Category]=[Harvested Category1]; Report1[Year]=[Harvested Year1])

[Sales2]=CALCULATE(SUM(Report1[Qty]), Report1[Category]=[Harvested Category2]; Report1[Year]=[Harvested Year2])

Table= FILTER(ALL(Report1[Account]), [Sales1] > 0 && [Sales2] =0)

You could follow a similar approach to select the comparison ">0", "=0", etc.