Apologies if this is covered later in the course- I am still working my way through.
So I created a measure that shows the % of total sales that a model is contributing for 2020 and one for 2019, and then a measure that shows the movement, so model x contributed to 10% of sales and 5% in 2019, the increase is 5%.
As I sell a lot of products I want to create a visualisation that only shows movements above or below a set %, but it seems that you can’t apply a filter to a measure only tables?
The measure I have created is:
% Model Movement = CALCULATE([% of 2020 Sales] - [% of 2019 Sales])
It’s true that the filter argument in a CALCULATE statement can’t reference a measure, BUT this limitation is easily remedied by adding a FILTER function to the CALCULATE statement.
Note that in your measure, the CALCULATE function isn’t actually doing anything, since it’s not changing filter context.
I think this issue is easily resolvable, but it would help if you could please post your PBIX file.
Thanks Brian for looking at this. I can’t share the PBIX as it has customer info in.
I have looked at the video, but again it uses tables rather than measures. The filter i want to apply has to be to the results of the calculation,
Rob
It’s a bit tricky to do this without having the data or the data model available, but one way to solve this would be to add a disconnected table to your model (say, called Thresholds) containing the percent values you want to evaluate the measure against (say, called Target Threshold). You could then add a slicer to your report based on the Target Threshold column. The boolean statement in the second argument of your FILTER function would then look something like:
[% Model Movement] >= SELECTEDVALUE(‘Thresholds’[Target Threshold])
I hope that’s helpful. If not, perhaps you could mask the customer data in your file and upload it so that we can work through the full measure with an actual data model and data.
It is possible to create a measure as you have, and then use the measure as a visual level filter in the visual you want to create. So add your desired visual to the report, presumably containing a list of models and other pertinent info, then drag the measure to the “Visual level filters” well and set the criteria for your measure.
Note that this does not work for page and report level filters.
