Distinct Count not working when selected all months

[Anonymous]

Hello

I am working on headcount data and looking for distinct Emp ID each month and accordingly year

Distinct count is working when i select single month , however when all months are selected (yearly unique employees active) the total number is coming correct but drill down metrics like gender and others are incorrect values.

Distinct count is arrived from selection of _hc=1 (1 is active employee and 0 is exit employee) and _ID - disctinct count i am doing

PBX is attached.I did comparison and values coming incorrect

Regards

[Anonymous]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create measure.

discount_id =
var _select=SELECTCOLUMNS('Sheet1',"1",'Sheet1'[Fiscal Month])
return
CALCULATE(DISTINCTCOUNT('Sheet1'[_id]),FILTER(ALL(Sheet1),'Sheet1'[Fiscal Month] in _select&&'Sheet1'[_job family]=MAX('Sheet1'[_job family])))

discount_id_total =
var _table=SUMMARIZE('Sheet1','Sheet1'[_job family],"_value",[discount_id])
return
IF(HASONEVALUE('Sheet1'[_job family]),[discount_id],SUMX(_table,[_value]))

2. Result:

Best Regards,

Liu Yang

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

[Anonymous]

thanks Liu..however the no is not matching total distinct count will be 1081Logic used , _hc =1 , removed duplicated _id and got this number as overall active on fiscal year

Also i need to show at gender level , org level etc .. the measure shared is not working as its linked with job family

[amitchandak]

@Anonymous , distinctcount is recalculated in grand total again. so it distinctcount  year not sum of month values

if you want sum of month value

then

sumx(values(Date[Month Year]), calculate(distinctcount(Table[emp id]) ) )

[Anonymous]

this is not working for me , the single month selection is correct but all months when selected - value coming incorrect