Hi
I have a pivot table that has a count column (Total Visits), a distinct count column (Unique Visitors) and a sum column (Consumption kWh). In the grand total the number for the distinct count column (Unique Visitors) is not the sum of the numbers above. I don't actually know what it is - it isn't the count or the average of those numbers either. The GTs for the count and sum columns are correct.
Is there any way of making the distinct count GT a sum of the numbers above?
Hi Mardi,
Please show me the measure formula for Consumption kWh and the row context for the PivotTable so I can understand how the data in the rows is being filtered. If it's confidential then please show the Row Label field name.
Thanks,
Mynda
Hi Mynda
Consumption kWh is Consumption Wh divided by 1000 to add a new column in the query (see attachment). Not sure what the second request means but I've put a screen shot of the whole pivot table in the attachment.
Hi Mardi,
I can't see anything obvious wrong that would explain the discrepancy, but I'll wait to check your file when you email it to me. I presume it's the same file as the one for your other question.
Mynda
It is, thank you so much!
Hi Mardi,
The problem occurs because the RFID that is being counted is not unique to a charge station i.e. the same RFID can appear in multiple charge stations, but the Distinct Count only counts it once in the total.
I've emailed your file as it's too large to upload here, but you'll see I've added a column to your fact table that concatenates the Charge Station and RFID into a new column and this is what is used for the total DISTINCTCOUNT.
Mynda
Thanks for doing this, it's much appreciated.
I've been looking at the DAX formula you used for the measure "Distinct Visits". It's an IF statement using the HASONEFILTER formula on the column "Charger Name". I'm not sure I understand HASONEFILTER; does it mean that if the Charger Name has been filtered to one value then use DISTINCTCOUNT of RFID, and if it has not been filtered to one value use DISTINCTCOUNT of "Station ID" (your new column)? So for the rows where there is a filter which returns one Charger Name it counts the distinct entries in the column RFID, and for the grand total where there is no filter it counts the distinct entries in Station ID?
Hi Mardi,
Correct. HASONEFILTER checks to see if there is a single filter applied to a row, Total rows are unfiltered, so it allows us to provide a different calculation for Totals and the other rows.
Mynda
