Question about syntax for using a measure as a filter within a measure

[RichardJ]

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

[RichardJ · Accepted solution]

@vanessafvg 

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 

[RichardJ · Accepted solution]

@vanessafvg 

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 

[vanessafvg]

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

[v-yiruan-msft]

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

[RichardJ · Accepted solution]

Thanks for the lesson @v-yiruan-msft - 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