Hi
I have two separate tables in Power BI, related to each other through a date table. Each one is very simple - just a list of months from July 20018 to April 2019 and then a column, which in one table is the number of gadgets installed that month ("Installations"), and in the other table is the cost of installing gadgets for that month ("Cost").
I am trying to create a matrix that has the months for the rows (from the date table) then the columns are "Gadgets Installed", "Cost of Installing Gadgets" (no problems so far), and then "Cost per Gadget".
To get Cost per Gadget I am trying to add a measure:
Hi Mardi,
Just because you know you only have one value for each month doesn't mean Power BI knows that. Typically you would have more than one value per month. Therefore, whether you have one value or multiple, you need to aggregate them before passing it to SUMX.
That said, I don't think SUMX is what you want. I expect you want to find the total cost of installing the gadgets for a month divided by the total number of gadgets installed. If so, you can use this formula for your measure:
=DIVIDE(SUM(Gadget Install Cost'[Cost]), SUM(Monthly_Installations[Installations]))
Mynda
Thanks Mynda, I'll give that a go.
It also occurred to me (while putting on make up this morning - why does the brain do that?) to merge the two tables and do a calculated column in the new table.
ETA: it worked perfectly! Thanks 🙂 I spent so much time on this yesterday and even flirted with the DIVIDE function but just couldn't join the dots.
🙂 glad you found a solution.
Just keep in mind that the merging method only adds up correctly because you have one value for each month. If you had more then it wouldn't be correct because the PivotTable would add the individual values from your custom column rather than taking the total cost divided by the total gadgets.
