Forum Discussion

Rockyalex's avatar
Rockyalex
Regular Visitor
4 years ago
Solved

Counting Data on ranked Dataset with filters

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's avatar
    v-zhangti
    4 years ago

    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.

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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])

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    4 years ago

    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's avatar
      Rockyalex
      Regular Visitor
      4 years ago

      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