Calculate rank on the fly based on the slicer and filter the rank and summarize the filtered data

[Anonymous]

Hi,

Scenario:                                                                                                                                                                                                                                                                                                                                                                                                             

I wanted to calculate rank on the fly based on the date slicer chosen and then I wanted to find the average of value and count of participants grouped by section for rank=1 participants

What I did to get rank on the fly:       

I have calculated two measures :

• Mindate measure
• Rank measure                                                                                                                                                  

Mindate Measure:                                                                                         

Date=min(Query2[date])    

Rank Measure :                                                                                

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

By using above two measures, I have calculated rank dynamically based on the date slicer     

Table with rank 

Participant	Date	Section	Value	Rank
100	1/3/2018 10:37	3	7	1
100	1/23/2018 10:36	22	4	2
100	3/22/2018 15:08	3	5	3
222	2/22/2018 14:41	3	6	1
222	3/20/2018 14:38	23	4	2
333	1/18/2018 10:41	22	9	1
444	1/11/2018 11:05	24	2	1
555	1/10/2018 14:23	22	6	1
555	1/30/2018 12:44	3	10	2
555	3/12/2018 16:19	23	2	3

I am facing problem to get the below results..                                                                                                                                                                                                                                                                                    

I am interested only in rank= 1 data & want the result as average of the value & count of participants by grouping Section for rank =1 participants  

Result 1:

To get rank =1 participants data

Participant	Date	Section	Value	Rank
100	1/3/2018 10:37	3	7	1
222	2/22/2018 14:41	3	6	1
333	1/18/2018 10:41	22	9	1
444	1/11/2018 11:05	24	2	1
555	1/10/2018 14:23	22	6	1

  Result 2: Summarized data for rank=1 participants                              

Section	Average of Value	Count of Participant
3	6.5	2
22	7.5	2
24	2	1

I would really appreciate if someone can help me on this..       

Thanks

Suman                                                                                                  

[Zubair_Muhammad · Accepted solution]

@Anonymous

Try these MEASURE

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

Count of Participant =
CALCULATE (
    DISTINCTCOUNT ( Query2[Participant] ),
    FILTER ( VALUES ( Query2[Participant] ), [Rank_] = 1 )
)

[Anonymous]

please help me in this..

thanks

[Zubair_Muhammad · Accepted solution]

@Anonymous

Try these MEASURE

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

Count of Participant =
CALCULATE (
    DISTINCTCOUNT ( Query2[Participant] ),
    FILTER ( VALUES ( Query2[Participant] ), [Rank_] = 1 )
)

[Anonymous]

Hi Zubair,

When I have slicer for participant, average measure is not calulcating properly

Please seee attached screenshot with highlighted : instead of 6 it should be 7

I have used this measure:

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

Thanks inadvance

[Anonymous]

Hi Zubair,

When I have slicer for participant, average measure is not calulcating properly

Please seee attached screenshot with highlighted : instead of 6 it should be 7

I have used this measure:

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

Thanks inadvance

[Anonymous · Accepted solution]

Hey Zubair, Thanks a lot..It worked..