Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
94 | |
82 | |
70 | |
64 |
User | Count |
---|---|
116 | |
106 | |
94 | |
79 | |
72 |