I have a pivot table with three columns - a count column (Total Visitors), a distinct count column (Unique Visitors) and a sum column (Consumption kWh). I have added measures to divide Consumption kWh by Unique visitors and it works fine:
=[Sum of Consumption kWh]/[Distinct Count of RFID]
However, dividing Consumption kWH by Total Visitors does not give the result I need:
=[Sum of Consumption kWh]/[Count of RFIDCard_id]
I have attached a screenshot of the result. Interestingly, dividing the Consumption kWh by the result of the measure for each row gives the same answer, which is 125.
Hi Mardi,
Are 'Sum of Consumption kWH' and 'Count of RFIDCard_id' explicit measures? They look like implicit measures to me.
Can you please share a screenshot of your Measures manager, expanded so I can see the whole measure, and the PivotTable field list, so I can understand the relationships.
Thanks,
Mynda
Hi
I think it might be easier for both of us if I upload the workbook - I've removed identifying data. I hope this is OK!
ETA: That's not going to work, I can't get it under 800kb.
Are 'Sum of Consumption kWH' and 'Count of RFIDCard_id' explicit measures? They look like implicit measures to me.
No... at least on the data model they show in the pane at the bottom of the data (see screenshot).
Can you please share a screenshot of your Measures manager, expandedso I can see the whole measure, and the PivotTable field list, so I can understand the relationships.
Is the screenshot I sent the Measures manager?
Hi Mardi,
Can you please email me the file website at myonlinetraininghub.com? The measures screenshot isn't what I meant. I want to see the formula, not the result of the formula. Anyway it would be a whole lot easier if I can see your file.
Thanks,
Mynda
Done, thanks 🙂
Hi Mardi,
Thanks for sharing your file. The measure Consumption per Visit kWh had an error. You should get in the habit of clicking the 'Check formula' button in the 'new measure' dialog box as it will help you troubleshoot. The problem was you were referencing a count on a field name that didn't exist. It should be:
=[Sum of Consumption kWh]/[Count of RFID]
Mynda
Brilliant! Thanks Mynda. I spent most of yesterday re-watching a lot of the Power Pivot training videos, which has helped with some other questions I had. The only problem is that your voice is so soothing that I fell asleep a couple of times 🙂
😀 glad I could help. Hopefully you subconsciously absorbed some of the information while sleeping!
