Aggregation problem in calculated column

[zudar]

EDIT: 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.. 😞

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. 

[zudar]

Link to PBIX-file: ShopInfo 

[Greg_Deckler]

Measure = MAX('Products'[YearlySales]) / MAX('ShopInfo'[Employees])

As I stated in my original reply, you need to use a measure and when using a measure, you need to use an aggregation and I indicated that you should likely go with MAX.

PBIX is attached but it is updated to latest version.

[zudar]

Help!? 😭

[Greg_Deckler · Accepted solution]

Right, if you use a measure, you will need to use an aggregation, just use MAX if your output is a table visualization.

If your output that you show is an actual table, you can create a calculated column and not have to use aggregation.

[zudar]

My output is supposed to be an actual table, just as I showed in the initial question.

When I create a calculated column (within any of the included tables), I still run into the issue of having some type of aggregation.

• In Products, I can use 'YearlySales', but not 'Employees' in my DIVIDE(...) formula (or even using the '/' operator) without aggregating it first.
• In Shops, I can't use either 'YearlySales' nor 'Employees' in my DIVIDE(...) formula (or even using the '/' operator) without aggregating them first.
• In ShopInfo, I can use 'Employees', but not 'YearlySales' in my DIVIDE(...) formula (or even using the '/' operator) without aggregating them first.

In all cases, whenever I use MAX(...), MIN(...), SUM(...), AVERAGE(...) to aggregate the field that 'needs' aggregation, I don't get the result I need.. 😞