Compare values in a row with a measure dynamically

[Anonymous]

I need help please

ID	Tier	Condition	Rate	Term	Year
1	A	New	0.2	5	2015
2	B	Used	0.5	5	2014
3	A	New	0.4	3	2014
4	C	Used	0.45	3	2015
5	D	Used	0.24	2	2015
6	C	Used	0.15	4	2014
7	A	New	0.75	2	2014
8	B	Used	0.8	1	2014
9	B	Used	0.35	1	2015
10	D	Used	0.12	6	2015
11	C	New	0.78	3	2015
12	A	Used	0.45	2	2014

considering this table, I would like to calculate the median Rate with the Year and Condition columns as slicers in the following way:

If 2014 is selected on the Year slicer for instance "Used" is selected on the Condition slicer, i would like the following output

ID	Tier	Condition	Rate	Term	Year	Median Rate	Rate Greater than Median
2	B	Used	0.5	5	2014	0.475	Yes
6	C	Used	0.15	4	2014	0.475	No
8	B	Used	0.8	1	2014	0.475	Yes
12	A	Used	0.45	2	2014	0.475	No

And if Year slicer is "2015" and Condition Slicer is "used" for instance, this should be the result

ID	Tier	Condition	Rate	Term	Year	Median Rate	Rate Greater than Median
4	C	Used	0.45	3	2015	0.295	Yes
5	D	Used	0.24	2	2015	0.295	No
9	B	Used	0.35	1	2015	0.295	Yes
10	D	Used	0.12	6	2015	0.295	No

Can anyone show me how i can achieve this please?

[v-lili6-msft]

hi, @Anonymous

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Best Regards,

Lin

[Anonymous]

Hi,

I havent't had time to check till now. However, I actually want the Rate Greater Than Median as a calculated column and not as a measure please

[v-lili6-msft]

hi, @Anonymous

First, You should know that the difference between calculated columns and measures. 

for your case, it's better to use measure instead of column.

Second, if you want create a column you could use EARLIER Function as below:

Column = 
IF (
    CALCULATE ( SUM ( Table1[Rate] ) )
        > CALCULATE (
            MEDIAN ( Table1[Rate] ),
            FILTER (
                Table1,
                Table1[Year] = EARLIER ( Table1[Year] )
                    && Table1[Condition] = EARLIER ( Table1[Condition] )
            )
        ),
    "Yes",
    "No"
)

Best Regards,

Lin

[Anonymous]

I intend to use this as a slicer and in another case in the column section of a matrix visual. A measure cant be used in these cases

[v-lili6-msft]

hi, @Anonymous

You could try to use this formula to create a measure as below:

Rate Greater than Median = IF(CALCULATE(SUM(Table1[Rate]))>CALCULATE(MEDIAN(Table1[Rate]),ALLSELECTED(Table1)),"Yes","No")

Result:

Best Regards,

Lin