Forum Discussion
Help needed with calculated dynamic column
Hi,
This is the problem I have:
- I am using a simple line chart with 2 lines (one for price, and one for raw material index)
- I want to add a third line for 'Raw Material Impact'
I run into the problem with the latter given the formulae behind:
I have created a table called 'RM impact', which includes a Month column, and a calculated 'RM impact' column. However, clearly, the calculated column with dynamic values does not really work.
My 'RM impact' formulae are as follows:
- RM impact Feb 2021 = [RM impact Jan 2021]*(1+([Raw Mat %]*([RM index Feb 2021]/[RM index Jan 2021]-1)))
- RM impact Mar 2021 = [RM impact Feb 2021]*(1+([Raw Mat %]*([RM index Mar 2021]/[RM index Feb 2021]-1)))
- RM impact Apr 2021 = [RM impact Mar 2021]*(1+([Raw Mat %]*([RM index Apr 2021]/[RM index Mar 2021]-1)))
- RM impact May 2021 = [RM impact Apr 2021]*(1+([Raw Mat %]*([RM index May 2021]/[RM index Apr 2021]-1)))...etc. until April 2022.How can I make so that this becomes then addable to the line chart mentioned above, that can then be sliced with various existing slicers?Any advice would be highly appreciated.
2 Replies
Giorgi1989 , You can not use slicer values in a column.
You can bucket a measure if needed
range based
Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1387187#M626
Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-kAbsolute value based
Dynamic segmentation -Measure to Dimension conversion: https://youtu.be/gzY40NWJpWQ
Thank you for the suggestion.
I seem to be close to resolving the issue I have.
The problem I have is with the formula below:
I want filters from 'RM index' table not to affect any of the measures in the formula below, with exception of 'Raw Mat%'.
RM impact Apr 2022 = CALCULATE([RM impact Mar 2022]*(1+([Raw Mat %]*([RM index Apr 2022]/[RM index Mar 2022]-1))),ALL('RM index')).Could it be possible within this context, to make it so that only 'Raw Mat%' is not affected by the ALL function? Thank you.
