2 Replies Latest reply on Nov 16, 2016 8:07 AM by Sherzodbek Ibragimov

# Aggregate a dimension based on filtered measure values

I'm new to Tableau and I'm working on a usage summary report. The table from my database looks like this. Not the greatest representation. It just means company AAA has product 1 installed on 50 devices.

Company Name Product 1 - No. of devicesProduct 2 - No. of devicesProduct 3 - No. of devicesProduct 4 - No. of devices
AAA50550100
BBB4507060
CCC5050500

This is just a sample table. The one I will be working with will have over 15 products and more than 10000 company names. The goal is to answer the following question based on this report.

Number of customers that use a certain combination of products. For example, a customer uses both product 1 and product 2 if : Product 1 - No. of devices > 0 AND Product 2 - No. of devices > 0. Here's what I did and I'm convinced there is a better way to do it.

I created the following  calculated fields

Product 12 = IF Product 1 - No. of devices > 0 AND Product 2 - No. of devices > 0 THEN 1 END

Product 23 = IF Product 2 - No. of devices > 0 AND Product 3 - No. of devices > 0 THEN 1 END

and so on.....

Later, I just counted the number of times these values were set to 1 , and used that to represent combinations.

Can someone suggest a better way to do this, keeping in mind that there is going to be an enormous number of combinations and will be very tedious to create a calculated field for every combination?

Thanks!

Number of customers with (Browser Total > 0)

• ###### 1. Re: Aggregate a dimension based on filtered measure values

Hope I understood it correctly. Will this work for you:

Used sample below:

 Company Product1 Product2 Product3 Product4 AAA 50 55 0 100 BBB 45 0 70 60 CCC 50 50 50 0 DDD 0 0 40 0

If so, you have to pivot all products and it will give your 2 new fields instead of 15 products. Pivot Name I changed to Products and Pivot Values i changed to Devices. Next is simple formula as above. See for more info attached.