Counting Data on ranked Dataset with filters

[Rockyalex]

Hi, I am trying to show count of distinct AgeGroup-ID for each country-ID from the top 20% of Data set.

so first i created Rank as 

Rank = RANKX(

ALLSELECTED('FACT-Population'[Country-ID],'FACT-Population'[AgeGroup-ID],'FACT-Population'[Gender]),

calculate(sum('FACT-Population'[Population])),

,

DESC,

Dense

)

below shows what i have created. based on the country selection or Gender selection i get the Data ranked correctly

I want to show Country and distinct count of AgeGroup-ID as a matrix for Rank <20 (ie. top 20% of my dataset) and also when i filter on Country ID or Gender the calcualtion should be applied on the filtered dataset.

The output i am expecting should look like

 

[v-zhangti · Accepted solution]

Hi, @Rockyalex 

You can try the following methods.

Measure:

CountAgeGroup-ID =
CALCULATE (
    DISTINCTCOUNT ( 'FACT-Population'[AgeGroup-ID] ),
    FILTER (
        ALL ( 'FACT-Population' ),
        [Rank] <= 20
            && [Country-ID] = SELECTEDVALUE ( 'FACT-Population'[Country-ID] )
    )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Rockyalex]

@v-zhangti ,

Thank you it worked perfectly. 

what if i want to see the Details of AgeGroup id under the Country in the Matrix ? currenly you cannot expand the Country ID to see what AgeGroup ID's are in it (currently .

Thanks & Regards,

Alex

[amitchandak]

@Rockyalex , Use rank in the visual level filter of create a measure like

Total Filter population = Sumx(filter(
Addcolumns( Summarize('FACT-Population'[Country-ID],'FACT-Population'[AgeGroup-ID],'FACT-Population'[Gender]),"_1"
calculate(sum('FACT-Population'[Population])), "_2",m [Rank]) ,[_2]<= 20) ,[_1])