Filled map shows incorrect numbers in tooltip

[dc189]

Hello,

I have a clustered column chart and a filled map on my dashboard. The bar chart shows the correct number of stores that opened and closed in the past 5 years but the tooltip in the map does not show the same numbers. What might be going wrong?

I am happy to share a pbix file but I don't see an icon in which to upload the file.

Any timely help would be greatly appreciated! Thank you!

[Sean]

You can upload to OneDrive, DropBox or similar and share the link to the file here.

[dc189]

Thanks for your quick reply Sean. The dropbox link to the file is https://www.dropbox.com/s/nk1hdlgnhn00e7b/Stores.pbix?dl=0. Hope you are able to access it.

[Sean]

Go to the Map - Color Saturation - select COUNT (NOT DISTINCTCOUNT) that changes it back to 5

[Sean · Accepted solution]

@dc189

Okay yes the problem is COUNT does not count blanks while DISTINCTCOUNT does

To illustrate what's happening look at the picture

I've written all Measures (and simulated your Page Level filters with Slicers)

So you've dragged the Store Opened field and you selected Disctinct count

it is adding 1 to the 5 because all stores that were closed have a blank Store Opened field

NOTE you are not performing any Status Check when doing this count because you are counting the Store Opened field

and because you are doing a distcinct count all closed stores will count as 1 as their Store Open fields will be blank

Hope this helps and makes sense!

Here are the measures for you to test and see

CountDistinct Stored Opened = 
DISTINCTCOUNT('Sheet1 (2)'[Store Opened])

CountDistinct Stored Opened No Blanks =
CALCULATE (
    DISTINCTCOUNT ( 'Sheet1 (2)'[Store Opened] ),
    FILTER ( 'Sheet1 (2)', 'Sheet1 (2)'[Store Opened] <> BLANK () )
)

 

[dc189]

I got it! Thank you so much Sean!