Forum Discussion

coolasia's avatar
coolasia
New Member
8 years ago

Powerpivot distinctcount return wrong values

Dear All

 

I am new to powerpivot and trying to convert my excel file worksheet into powerpivot because my data is

getting huge and hard to managed.

 

I need to calculate unique value in a column of my excel. Therefore I use the function in powerpivot below

 

Unique ID:=Distinctcount(Total[ID])

 

It give me a result of Unique ID =4328 (Wrong )

 

Unfortunately this result is wrong.

 

The correct result is 4348 but i cannot get this correct result.

 

My data contain text, numer, -, . ...etc

 

Can someone advise how i can solved this problem ?

 

Thanks

 

regards

Ken 

 

 

3 Replies

    • coolasia's avatar
      coolasia
      New Member
      8 years ago

      Hi 

      Thanks for your reply..I have refresh but still same problem

      I suspect my problem with powerpivot is due to my data in the excel file.

      My excel file consist of data  with number and text. I notice that there is no

      problem for my data if it is TEXT, mixture of text & number or number that within 8 digit

      1.  BYTRUCK9010527936  (no problem)
      2. 25308407 (no problem)

      But when number become too long like below

      1. 43430660612014 , it will store the data in excel as “4.34307E+13”
      2. 43430660701011, it will store the data in excel as “”4.34307E+13”

      When Powerpivot extract the data, it will store it as “4.34307E+13” and count as 1 instead of 2.

       

      Any advise how to solve this kind of problem ?

       

      regards

      Ken