10 Replies Latest reply on May 13, 2016 1:26 AM by Ashish Chaudhari

# How to divide 2 measures from different grouping?

Hi all,

I have a source data like this

KPI
ProductTotal
A. Total All ProductProduct A1000
A. Total All ProductProduct B900
B. Total Profitable ProductProduct A500
B. Total Profitable ProductProduct B400

Display in tableau sheet

KPI
ProductTotal
A. Total All ProductProduct A1000
Product B900
B. Total Profitable ProductProduct A500
Product B400
Penetration B to AProduct A=(500/1000)=50%
Product B=(400/900)=44.44%

How can I get the 2 row as in RED? Do I need to create a calculated field?

• ###### 1. Re: How to divide 2 measures from different grouping?

Hi Yew,

Please find the attached screenshot and Formula.

Calc_Penetration

SUM({ FIXED [KPI],[Product Name]: SUM(IF [KPI]="B. Total Profitable Product" then  ([Total]) END) })

/

SUM({ FIXED [KPI],[Product Name]: SUM(IF [KPI]="A. Total All Product"  then  ([Total]) END) })

Thanks and Regards,

Ashish Chaudhari

3 of 3 people found this helpful
• ###### 2. Re: How to divide 2 measures from different grouping?

Please find the attached workbook for the same. This is in tableau 9.3 version.

Thanks once again.

1 of 1 people found this helpful
• ###### 3. Re: How to divide 2 measures from different grouping?

Thanks Ashish!

• ###### 4. Re: How to divide 2 measures from different grouping?

HI,

this

• ###### 5. Re: How to divide 2 measures from different grouping?

Hi Ashish,

Sorry, I have more questions...actually my actual is look like this..how to achieve?

 2016-01 2016-02 2016-03 A. Total All Product Product A 1000 2000 2500 Product B 900 1000 1200 B. Total Profitable Product Product A 500 600 700 Product B 400 100 200 Penetration B to A Product A =(500/1000)=50% =(600/2000)=30% =(700/2500)=28% Product B =(400/900)=44.44% =(100/1000)=10% =(200/1200)=16.67%
• ###### 6. Re: How to divide 2 measures from different grouping?

ok. Give me little time to fix it.

• ###### 7. Re: How to divide 2 measures from different grouping?

Hi Yew,

Please find the solution (.twbx and excel sample data).

SUM({ FIXED [Year],[KPI],[Product Name]: SUM(IF [KPI]="B. Total Profitable Product" then  ([Total]) END) })

/

SUM({ FIXED [Year],[KPI],[Product Name]: SUM(IF [KPI]="A. Total All Product"  then  ([Total]) END) })

Thanks and Regards,

Ashish Chaudhari

3 of 3 people found this helpful
• ###### 8. Re: How to divide 2 measures from different grouping?

Thank you!!!

I just started to learn this tool, so glad I can get help from someone master like you so fast!

• ###### 9. Re: How to divide 2 measures from different grouping?

Hi Ashish,

Just wonder can the result display as below? instead have another sheet to only display the %?

I want to append the % data at the bottom part.

 2016-01 2016-02 2016-03 A. Total All Product Product A 1000 2000 2500 Product B 900 1000 1200 B. Total Profitable Product Product A 500 600 700 Product B 400 100 200 Penetration B to A Product A 50% 30% 28% Product B 44.44% 10% 16.67%
• ###### 10. Re: How to divide 2 measures from different grouping?

Hi Yew,

I tried making this possible. Please refer to the screenshot below which I could manage to do. Please find the attached workbook.

I tried setting KPI as null for the calc_Penetration but couldn't do so.

The output is repeated for both the KPI. I am not able to get rid of it.

I have made below changes to the formula. (Removed KPI from the dimension list)

SUM({ FIXED [Year],[Product Name]: SUM(IF [KPI]="B. Total Profitable Product" then  ([Total]) END) })

/

SUM({ FIXED [Year],[Product Name]: SUM(IF [KPI]="A. Total All Product"  then  ([Total]) END) })

Let me know if you made it possible.

Thanks and Regards,

Ashish Chaudhari

3 of 3 people found this helpful