Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Calculated column returning wrong value

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?  
 

 

Need Help
Tutorial Requests
  • johnt75's avatar
    johnt75
    3 years ago

    Try

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

4 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    Try

    flag cliente =
DIVIDE (
    1,
    CALCULATE (
        COUNTROWS ( BASE_BANCO2_20221223 ),
        ALLEXCEPT ( BASE_BANCO2_20221223, BASE_BANCO2_20221223[ID CLIENTE] )
    ),
    0
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

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

      • johnt75's avatar
        johnt75
        Super User
        3 years ago

        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.