Forum Discussion
An impossibly simple division
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.
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])) )
2 Replies
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])) )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
