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.
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.
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
Solved! Go to Solution.
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 @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
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
Proud to be a Super User!
@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
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
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 |
---|---|
107 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |