Power Pivot DistinctCount by Groupby measure returning incorrect value

[tbobolz]

Hi All,

I have looked at this formula for an hour an can no tfigure out why it is returning a count of 32, when it should be 40. Any ehlp offered would be GREATLY appreciated. Focusing on B21 of the summary tab it displays 32, when I filter and pivot on the Raw data tab, they add up to 40.

I am at a loss!

Thanks for any help you can offer.

Link to workbook

 

[jorismoerings]

Hi,

You're misinterpreting the measure you've created.
A COUNT will never be a SUM.
By using the DISTINCTCOUNT combined with GROUPBY the results per day are correct however the total mentioned in the pivot isn't a sum but is a distinct count.
So in this case you're doing a distinct count per month, that calculation disregards the days and only looks at the distinct count for the month.

 

[jorismoerings]

As addition. This can be solved by creating a extra measure, like this:

Distinct Visits2:=sumx(distinct(Table1[Service Date]),[Distinct Visits])

 

[tbobolz]

Ahhhh, yes the light bulb just went on. It's counting ths distinct total for the month in my pivot.

Thank you very much for the lesson!

 

[jorismoerings]

Happy to help.