An impossibly simple division

[Anonymous]

Link to PBIX-file: ShopInfo

Hi all!

I could use some help wrapping my head around the following issue:

I have three tables in my model:

They look like:

Now I've created the following table using these tables:

Now, I want another column in this table that will divide the number of Employees over the YearlySales. So..

ProductID	Employees	YearlySales	YearlySales per Employee
348	10	3000	300
348	20	3000	150
348	50	3000	60
532	5	1000	200
532	10	1000	100
924	5	1500	300
924	10	1500	150
924	20	1500	75
924	50	1500	30

In the table, I have ticked the 'Don't Summarize' options for the columns 'Employees' and 'YearlySales'.

But as I try to create a measure or calculated column in the ShopInfo-table which just divides both values, the formula editor requires me to enforce some kind of aggregation (max, min, sum, etc...) before being able to include the YearlySales. Which in turn messes up the calculation.. I tried a lot of variations, but no success. 😞

Any ideas on how to solve this? I'm even questioning if I should create a measure or calculated column. Help is very much appreciated. 

[camargos88]

Hi @Anonymous ,

You need to change the direction of relationship to both between Shops and ShopInfo.

Also create this measure:

Measure = DIVIDE(SUM(Products[YearlySales]); SUM(ShopInfo[Employees]))

Ricardo

[az38 · Accepted solution]

Hi @Anonymous 

in depends on your use case scenario

but in the most common case you can try a measure

YearlySales per Employee = 
DIVIDE(
CALCULATE(MIN(Products[YearlySales])),
CALCULATE(MIN(ShopInfo[Employees]))
)