Based on the slicer calculate rank dynamically and summarize data for rank =1

[Anonymous]

Hi Team,

I have calculated rank dynamically by :

Rank Measure :                                                                                

Rank = rankx(filter((Query2),Query2[Participant]=min(Query2[participant])),[MinDate],,1) 

Rank is working fine.

The problem is when I want group by section and find the average of the data for rank=1 for the chosen participant from the slicer ,its not working with the below measure.

Average Value =
CALCULATE (
    AVERAGE ( Query2[Value] ),
    FILTER ( VALUES ( Query2[Participant] ), [Rank] = 1 )
)

Below example in the screenshot the average should be 7 but with the above measure it is showing as 6 whi h is incorrect.

Please help me on this..

Thanks..

[v-yuezhe-msft]

@Anonymous,

I create a summarized table as shown in the following PBIX file, and then calculate the average value.

https://1drv.ms/u/s!AhsotbnGu1NokzrR5HvyEnrq4zqB

Regards,
Lydia

[Anonymous]

Hi @v-yuezhe-msft

Thanks for the help..I t really worked except a small problem with rank..

Actually,you have created column rank but I need a measure rank. The reason is I want the rank to change dynamically based on date slicer. As far as I know, Column rank don't work dynamically..

See screenshot When I change the slicer the column rank is showing the rank that is already there but not dynamically changing the rank.

I have added a measure rank ,

 Rank_Measure = rankx(filter(ALLSELECTED(Query2),Query2[Participant]=min(Query2[Participant])),CALCULATE(min(Query2[Date])),,1)

But calculatetable DAX  not allowing to put rank_measure 

Calculated table is not dynamic , it means when date slicer is changed the rank would change but that changed data is not updated in the calculatedtable...

I want the calculated table to be dynamic ...

please help on this 

Thanks

Thanks