Showing results for 
Search instead for 
Did you mean: 
Helper I
Helper I

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

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








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


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


Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper I
Helper I

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

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 






Helpful resources

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!


Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors