Forum Discussion
filtering using calculated fields
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
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.
3 Replies
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
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
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.
