Forum Discussion
Aggregation problem in calculated column
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.
- 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.
5 Replies
- 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.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.. 😞
Link to PBIX-file: ShopInfo
- 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.