calculating a column value based on values selected in slicer dynamically

[Anonymous]

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

[v-kelly-msft · Accepted 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.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

@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.

[Anonymous]

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?

[v-kelly-msft]

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].

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

@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

[Greg_Deckler]

@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.

[Anonymous]

@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.