Forum Discussion
Responsive ResidentQuestion about syntax for using a measure as a filter within a measure
Hi,
I have the following Matrix which displays the correct values for the measures highlighted in yellow.
I've been (so far unsuccessfully) trying to update the card circled in blue which has its own measure.
Measures within a Matrix
The Matrix has a visual filter which checks whether the [% Change] measure value is greater than 1.01
Im trying to get the total for the Cost Impact Column of the figures shown in the Matrix.
In previous measures I've been able to apply filters to obtain the results as i've been filtering column contents.
I'm not sure if/how to create a Measure called:
Cost Impact of the Price Rise
to determine
The SUM of the [Cost Impact]
where
[% Change] is greater than 1.01
Note that [% Change] and [Cost Impact] are measures not columns in a table.
The pbix file can be found here : https://www.dropbox.com/s/yv3a1sa0e57dx2c/Previous_Month_Price_v3.pbix?dl=0
Any help would be welcome,
Cheers,
Richard
I ended up going down a different path to resolve this.
Used a calculated column to obtain the previous price
Previous Price Entry Date = CALCULATE(MAX('Stock Part Pricing Over Time Analysis'[Report Creation Date]), FILTER('Stock Part Pricing Over Time Analysis', 'Stock Part Pricing Over Time Analysis'[Report Creation Date] < EARLIER('Stock Part Pricing Over Time Analysis'[Report Creation Date])))which let me source the previous months price onto the current row.
The arithmetic for the remaining fields was easy to calcuate once I had the current and previous prices on the same row.
This meant the matrix's and cards showed the correct totals.
Cheers,
Richard
Thanks for the lesson Anonymous - Really nice work.
The combination of measures works perfectly and I wouldn't have thought of this approach.
If it's of any use to anyone the updated pbix file containing the new measures can be found here:
https://www.dropbox.com/s/k6zslvb0yf7nvfz/Previous_Month_Price_v4.pbix?dl=0
Cheers,
Richard
5 Replies
RichardJI ended up going down a different path to resolve this.
Used a calculated column to obtain the previous price
Previous Price Entry Date = CALCULATE(MAX('Stock Part Pricing Over Time Analysis'[Report Creation Date]), FILTER('Stock Part Pricing Over Time Analysis', 'Stock Part Pricing Over Time Analysis'[Report Creation Date] < EARLIER('Stock Part Pricing Over Time Analysis'[Report Creation Date])))which let me source the previous months price onto the current row.
The arithmetic for the remaining fields was easy to calcuate once I had the current and previous prices on the same row.
This meant the matrix's and cards showed the correct totals.
Cheers,
Richard
- vanessafvg
Community Champion
hi richard are you able to provide some data or dummy data?- RichardJ
vanessafvg Thanks for the reply.
Sorry for the delay - I was creating the pbix containing test data
link : https://www.dropbox.com/s/yv3a1sa0e57dx2c/Previous_Month_Price_v3.pbix?dl=0
Cheers,
Richard
Hi RichardJ ，
It's glad to hear that your problem has been resolved. And thanks for shairng your solution here. I create 2 measures as below for your requirement, please check if they are what you want. If no, please just ignore it...
Cost Impact of Price Rise = if([% Change]>1.01,SUMX(VALUES('Stock Data'[Material No]), [Cost Impact]),BLANK())Measure = SUMX(VALUES('Stock Data'[Material No]),[Cost Impact of Price Rise])Best Regards
Rena
