Nested IF to calculate Sales of different bundles of products

[rm1]

Hello everyone,

I need help to calculate percentage of sales of different bundles of products, based on department selection. Currently I'm using the following formula to calculate Department 1 bundle of products:

sum({$<Product={'product1','product2',etc}, year={2019}>} [quantity product])
/
sum({<year={2019}>} total [quantity product])

It works fine, but I need to be able to change my filter to Dept 2 and Dept 3 and recalculate the Sales percentage based on a second and a third list of products. How can I modify the previous formula? I thought about a nested IF given the fact that I need to work on 3 Depts.. but I failed at my first attempt.

Thanks in advance!

[ChiragPradhan]

Hi,

It's best to create a department column in your data model for the products that belong to that department. 

You can do that in the script by using ApplyMap().

Makes your expressions more efficient and readable.

Regards,

Chirag

[rm1]

Yes, you are right. But I don't have the permission to modify the data model.. I can only work on front end dashboard. 

I thought about a structure like the following, but it continues to give me an error message.

SUM (IF ([Dept name]='Dept.1', {$<Product={'Product1', 'product2'}, year={2019}>}),  IF ([Dept name]='Dept.2', {$<Product={'Product3', 'product4'}, year={2019}>}))
/
sum({<year={2019}>} total [quantity product])

I'm new on Set Analysis and I'm not able to find the error inside the line.