Forum Discussion

vkomarag's avatar
vkomarag
Helper III
9 years ago
Solved

Calculate RANK and FILTER the ranks dynamically based on year.

Hi I have the below data   ID year question rating 1 2015 q1 1 2 2015 q1 3 3 2015 q1 4 4 2015 q1 2 1 2016 q1 1 2 2016 q1 2 3 2016 q1 5 4 2016 q...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    Hi vkomarag

     

    Do you only ever care about the most recent two years?

     

    You could try adding these measures to determine the values you need

     

    CURR_YEAR = MAX('Table1'[year])
    PREV_YEAR = MAX('Table1'[year]) - 1

    Then other measures could use these eg.

     

    Curr_Year Average = CALCULATE(
						AVERAGE('Table1'[rating]),
						FILTER(
							'Table1',
							'Table1'[year]=[CURR_YEAR]
							)
						)
vkomarag's avatar
vkomarag
Helper III
9 years ago

Hi Phil_Seamark

 

Thanks for your solution.

 

Is there anyway that we can do the same thing without using calculate. The reason is.

 

I am calculating current year and previous year average as measure and i am trying to calculate a column based on these two columns,

 

Indicator=IF(curr_year>prev_year,"Up","Down");

 

When I am trying to do the above calculation, I am facing circular dependency error and unable to create the indicator field.When i went through a blog reading about circular dependency i came to know that this is because of CALCULATE function.

 

Any help on this appreciated

 

Thanks

KVB

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

Could you do the Indicator as a calculated measure instead of a calculated column?