Powerpivot : pb with =COUNTROWS(DISTINCT in

[kakemphaton]

Hello,

I have a problem with this formula =COUNTROWS(DISTINCT(AO_SQL[unique_key])). The results is wrong because, all duplicated rows aren't identified. My table is structured like this :

Year Month unique_key place area
2012 5 2565 France Nantes
2010 5 3256 Germany Berlin
2010 5 3256 UK London


A Unique key could have multi rows with data in place and area.

The right result for 3256 is 1 row. This formula is created in measure in Excel. This formula works if i want to count unique key, but when i put "area" or "place" in column labels, the result is wrong, because Excel considers Germany and UK like 2 rows, or i would like Excel count 1 row but not 2. I would like Excel show one of the two rows, the best will be show the firt row in my table, so Germany.

Sorry, my english is bad

Thanks You

 

[miguel.escobar]

I believe you are using Powerpivot v1.0 and that formula SHOULD work, could you upload a copy of the workbook to see what you're trying to achieve?

 

[kakemphaton]

Hello,

http://kakemphaton.free.fr/Test.xlsx

It's A copy. The first pivot table shows 3584 rows, but the second shows 3588 and the third 3615, i wish obtain the same result for the three pivot table : 3584.

Thanks !

 

[miguel.escobar]

Hello,

http://kakemphaton.free.fr/Test.xlsx

It's A copy. The first pivot table shows 3584 rows, but the second shows 3588 and the third 3615, i wish obtain the same result for the three pivot table : 3584.

Thanks !

Actually, all of them show 3584 but you need to enable the GRAND TOTAL for rows and columns instead of doing a sum of all the values in the values field. What happens is that the same ID is actually displaying in several department#'s at the same time.

It's like saying that the same customer goes to multiple stores but at the end, it's just 1 customer that purchased in several stores.

Hope this helps!
Miguel

 

[kakemphaton]

It's Works !

Thank you ! I haven't think this way !