cancel
Showing results for
Did you mean:
Highlighted
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 =
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.

Thanks..

2 REPLIES 2
Highlighted
Microsoft

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

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

Regards,
Lydia

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.
Highlighted
Helper I

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

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

Thanks

Thanks

Announcements

#### 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

#### 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