I'm very new to Power BI. I don't know if what I'm trying to do is impossible or just way beyond my abilities!
I have data in a number of tables. I'm using two of those in a report table (1 Report table.pdf). The columns "Unique Users" and "Total Vists" are from the same column (RFIDCard_ID) using the Count (distinct) and Count functions. The table from which all the relevant data in my report table comes is shown as 2 Data table.pdf.
I would like to add columns to my Report Table that are calculations of the numbers in that table, which are:
Consumption Wh / unique users
Consumption Wh / total visits
Hours charging / total visits
And this is what I don't know how to do. I am familiar with Power Query so I know how to add a calculated column in that but that doesn't give me what I need, which is an overall calculation. I've prepared an Excel table showing what I need the Power BI Report Table to look like (3 Excel example.pdf).
Hi Mardi,
You can use the DIVIDE function to add measures to your Power Pivot model that takes Consumption Wh / Unique Users etc. i.e.
=DIVIDE([Consumption kWh] / [Unique Users])
This assumes you have explicit measures already in place for Consumption kWh and Unique Users, otherwise you'll have to calculate them like so:
=DIVIDE(SUM(Table1[Consumption kWh]) / SUM(Table1[Unique Users])) Note: Table1 is a guess at your table name. You'll have to change it accordingly. Mynda
Hi Mynda
Thanks for coming to the rescue!
However, I'm not sure what to do... My data is in PowerBI, imported from a number of Excel workbooks, which I've not touched. I've been using PBI's queries to make changes. To do what you say above do I need to go back to the original Excel workbook for the table, create a query and load it into the data model, then build a pivot table and create the measure? If so, how do I then get the measure to show in Power BI so my table in the Reports area shows the result? Or have I missed the point entirely?
Thanks
Mardi
I've worked it out - no need to answer!
I right clicked the Values area in the Report and chose New Quick Measure, which brought up a simple dialogue box from which I could select Divide thenput in a numerator and denominator. This built a formula like this:
TotalConsumptionkWh divided by Count of RFIDCard_id =
Hi Mardi,
Glad you discovered the quick measures, but this won't get you very far before you need to learn how to write your own DAX formulas for custom measures you need.
I recommend you revisit the Power Pivot course sessions on Measures and then watch session 3.06 of the Power BI course to see the small differences with creating measures in Power BI.
Mynda
