0

I have a matrix that has the total premiums for each year, and total commissions for each year.

I would like a third column that shows what the ratio for total commissions to total premiums is for each year. I thought this would be easy but the values I get aren't right at all. Here's the formula I tried:

Current formula
Current formula

And here's how the column, Gross Commission %, came out:

Current table
Current table

Obviously this is all wrong. I've tried using DIVIDE([Gross Commission (+ve)],[Gross Written Premium]) instead but that didn't work either.

Obviously I've scrambled all of the premium and commission data before posting here so what you see is not real-world data, but it's exactly the same for the real data.

Rabbid76
  • 202,892
  • 27
  • 131
  • 174
Raphaël Briand
  • 1
  • 2
  • If those two "Gross" columns in your table are measures, then try your current formula without wrapping them in `SUM`. – Alexis Olson Jan 10 '18 at 17:40
  • I believe that's what I did here? "I've tried using DIVIDE([Gross Commission (+ve)],[Gross Written Premium]) instead but that didn't work either." – Raphaël Briand Jan 11 '18 at 09:07

1 Answers1

0

I would use the same formula, but created as a Measure, not a Column. So from PBI Desktop's Modeling ribbon, click New Measure.

Then the calculation will run after the default Sum calculation has occurred on the other columns, and will obey the context of the Year.

Mike Honey
  • 14,523
  • 1
  • 24
  • 40