Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello all,
I have been stuck on something for a while. Your help would be appreciated. I have a slicer of 'Year' which consists of years from 2015 to 2017
and my table values looks something like this:
countries | Year | Value |
C1 | 2015 | 24 |
C2 | 2015 | 12 |
C3 | 2015 | 31 |
C1 | 2016 | 12 |
C2 | 2016 | 54 |
C3 | 2016 | 13 |
C1 | 2017 | 44 |
C2 | 2017 | 23 |
C3 | 2017 | 56 |
I want to calculate a new colum delta which, for each, country holds the values calculated based on the years selected in the slicer.
delta for c1= (value of c1 for year 2017-value of c1 for year 2016-value of c1 for year 2015)/(max value between 2015,2016 and 2017))*100. Same for C2 and C3
So at the end I can produce something like:
countries | 2015 | 2016 | 2017 | delta |
C1 | 24 | 12 | 44 | 18.18% |
C2 | 12 | 54 | 23 | 79.62% |
C3 | 31 | 13 | 56 | 21.42% |
the only complication being that the years will be dynamically selected from the slicer.
For Example if the 'Year' slicer has only '2015' and '2016' selected than the delta is calulcated accordingly. If only one year is selected than delta shows 'NA'
Please kindly provide some insights as to how it can be achieved.
Thanks
Solved! Go to Solution.
Hi @Anonymous ,
You need 3 measures as below:
_difference =
var _year=MAXX(FILTER(ALLSELECTED('Table'),'Table'[countries]=MAX('Table'[countries])),'Table'[Year])
Return
CALCULATE(SUMX(FILTER(ALLSELECTED('Table'),'Table'[countries]=MAX('Table'[countries])&&'Table'[Year]=_year),'Table'[Value])-CALCULATE(SUM('Table'[Value]),FILTER(ALLSELECTED('Table'),'Table'[countries]=MAX('Table'[countries])&&'Table'[Year]<MAX('Table'[Year]))))
_maxvalue = CALCULATE(MAXX(FILTER(ALLSELECTED('Table'),'Table'[countries]=MAX('Table'[countries])),[Value]))
Measure 2 = IF(SUMX(FILTER(ALLSELECTED('Table'),'Table'[countries]=MAX('Table'[countries])),'Table'[Year])<=MAXX(FILTER(ALLSELECTED('Table'),'Table'[countries]=MAX('Table'[countries])),'Table'[Year]),"NA",IF(SELECTEDVALUE('Table'[Value]) IN FILTERS('Table'[Value]),MAX('Table'[Value]), FORMAT(ABS(DIVIDE('Table'[_difference],'Table'[_maxvalue])),"percent")))
Finally you will see:
For the related .pbix file,pls click here.
Hi @Anonymous ,
You need 3 measures as below:
_difference =
var _year=MAXX(FILTER(ALLSELECTED('Table'),'Table'[countries]=MAX('Table'[countries])),'Table'[Year])
Return
CALCULATE(SUMX(FILTER(ALLSELECTED('Table'),'Table'[countries]=MAX('Table'[countries])&&'Table'[Year]=_year),'Table'[Value])-CALCULATE(SUM('Table'[Value]),FILTER(ALLSELECTED('Table'),'Table'[countries]=MAX('Table'[countries])&&'Table'[Year]<MAX('Table'[Year]))))
_maxvalue = CALCULATE(MAXX(FILTER(ALLSELECTED('Table'),'Table'[countries]=MAX('Table'[countries])),[Value]))
Measure 2 = IF(SUMX(FILTER(ALLSELECTED('Table'),'Table'[countries]=MAX('Table'[countries])),'Table'[Year])<=MAXX(FILTER(ALLSELECTED('Table'),'Table'[countries]=MAX('Table'[countries])),'Table'[Year]),"NA",IF(SELECTEDVALUE('Table'[Value]) IN FILTERS('Table'[Value]),MAX('Table'[Value]), FORMAT(ABS(DIVIDE('Table'[_difference],'Table'[_maxvalue])),"percent")))
Finally you will see:
For the related .pbix file,pls click here.
@v-kelly-msft Thank you for this solution. I had to make a few changes to get the exact results owing to how the data is in my project but this solution helped me go a long way.
Hi @v-kelly-msft ,
I studied the pbix file and the solution looks great the only problem I am facing is that the 'Table'[Value] field I have is a measure. And so I am not able to create the first measure. I probably should have mentioned it in the initial question (my bad 😞 )
Any idea to proceed with this?
Hi @Anonymous ,
If table[value ] is a measure ,just remove the funtion before it,such as sum(table[value]) should be modified as table[value].
@v-kelly-msft Hi, i tried doing that but it is sill not possible to create Measure2 as the measure Value is not available for the IF condition in the SELECTEDVALUE function and in IN FILTERS clause
@Anonymous - So when you say calculate a new column delta, you are referring to a column in your visual correct? Not a column in the table, right? Because if it needs to respond to user interaction it will need to be a measure.
@Greg_Deckler I think it will need to be a column.
I am not able to understand how I can have it as a measure and give appropriate results for each country. Again, not really sure I'm a Power BI beginner.