Count distinct incorrect on top 20% ranked Data

[Rockyalex]

Hi ,

incorrect distinct employee counts are populating in Matrix created by Department and Team.
I have a EMP table which has Average Hours stored on monthly basis.

The requirement is that I need to populate a Matrix showing distinct count of Employees from top 20% based on slicers used are  Month, Dept,Team, so i created following measures

Table Columsn

MONTHNUMBER	DEPT	TEAM	EMPLOYEECODE	AVGHOURS
Jan	A	1	AX1	8.3

Rank Calculation

Rank= RANKX(ALLSELECTED('EMP'[EMPLOYEECODE],[AVGHOURS],,ASC,Dense)

COUNTROWS=ROUND(COUNTROWS(CALCULATE('EMP[EMPLOYEECODE),ALLSELECTED('EMP'[EMPLOYEECODE))*0.2,0)

This gives rowcount of 20% of selected value

EmployeeCount= CALCULATE(DISTINCTCOUNT('EMP'[EMPLOYEECODE]),FILTER(ALLSELECTED('EMP'EMPLOYEECODE),RANK <=[COUNTROWS]))

Above calcualation i have used to calculate the distinct employee count in the Top 20% bucket

Any help is appreciated.

[v-yiruan-msft]

Hi @Rockyalex ,

You can refer the following links to get it.

Display TOP N by Count base on 1 Field

Get % of total count while displaying top 10 in Power BI

If the above one can't help you get the desired result, please provide more raw data in EMP table (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[amitchandak]

@Rockyalex , Try like

EmployeeCount= CALCULATE(Countx( FIlter( Values('EMP'[EMPLOYEECODE]),[RANK] <=[COUNTROWS]), [EMPLOYEECODE]) )

refer this one from Matt

https://exceleratorbi.com.au/pareto-analysis-in-power-bi/

[Rockyalex]

Hi Amit, It didn't work.
the formula didnt evaluate the higlighted column

EmployeeCount= CALCULATE(Countx( FIlter( Values('EMP'[EMPLOYEECODE]),[RANK] <=[COUNTROWS]), [EMPLOYEECODE]) )

Thanks,

Alex

[Rockyalex · Accepted solution]

Hi Both,

Thank you for your support,

I have resolved this by applying Rank logics as below.

Rank= CALCULATE(RANKX(FILTER(

ALL('EMP'[EMPLOYEECODE]),[AVGHOURS],,ASC,Dense),ALLSELECTED(DEPT),ALLSELECTED(TEAM),ALLSELECTED(MONTHNUMBER)),

Kind Regards,

@Rockyalex