Calculated column returning wrong value

[Anonymous]

Hey, im having problem with a calculated column. I have a column with ID CLIENTE (more than 50K lines) including repeated values and I need to do a new column that works with a kind of COUNTIF (returning how many times this ID appear in the column ID CLIENTE) and divide it by 1 (the max value that I can have is 1) so I did this column called "flag client" with this DAX syntax 

flag cliente = DIVIDE(1, CALCULATE(COUNT(BASE_BANCO2_20221223[ID CLIENTE]), BASE_BANCO2_20221223[ID CLIENTE] = BASE_BANCO2_20221223[ID CLIENTE]), 0)

Im getting the exact correct value doing the same thing on Excel using a 1/COUNTIF with the result = 24.424

But for Power Bi im getting the sum of the column = 25277

It seems the column is not working properly (returning the correct value) since I have too much data, I already fixed by using DistintCount for ID Cliente but still not what I want because I need in the decimal way to reference in others situations. Can someone help me?  

[johnt75 · Accepted solution]

Try

flag cliente =
DIVIDE (
    1,
    CALCULATE (
        COUNTROWS ( BASE_BANCO2_20221223 ),
        ALLEXCEPT ( BASE_BANCO2_20221223, BASE_BANCO2_20221223[ID CLIENTE] )
    ),
    0
)

[Anonymous]

It worked!! but why can you explain what's the point of using countrows and using allexcept? 

[johnt75]

COUNTROWS is just a bit more optimised I think, so I tend to use that rather than COUNT.

The real key is the ALLEXCEPT. When you are creating a calculated column every column in the table is part of the filter, ALLEXCEPT removes the extra columns and you can just keep the columns that you need to be in the filter. This also helps to avoid circular dependency errors if you add multiple calculated columns to the same table.

[Anonymous]

Thanks for the solution and explanation!!