
1. Re: Need help in calculating % of total by splitting the values for one of the dimension value for other 2
Simon Runc Mar 8, 2017 4:10 AM (in response to Ashish Chaudhari)hi Ashish,
Just a bit of clarification...you say A, A/B, and B are coming from a single category column. Does this mean they are all in a single column, with the dimension A, A/B, or B
Type Value A 100 A/B 20 B 130
A 200 A/B 50 ... ... or do you have a separate column for A, A/B, and B as per your Excel?

2. Re: Need help in calculating % of total by splitting the values for one of the dimension value for other 2
Nikunj Bhardava Mar 8, 2017 4:13 AM (in response to Ashish Chaudhari)Ashish  Do you want to build this table in tableau?

3. Re: Need help in calculating % of total by splitting the values for one of the dimension value for other 2
Ashish Chaudhari Mar 8, 2017 4:14 AM (in response to Simon Runc)Hi Simon,
Your tabular representation is right. Single column contains values as (A,B,A/B). If you describe the column you will get these 3 values.
Regards,
Ashish C.

4. Re: Need help in calculating % of total by splitting the values for one of the dimension value for other 2
Ashish Chaudhari Mar 8, 2017 4:15 AM (in response to Nikunj Bhardava)Yes but in tableau.
Regards,
Ashish C.

5. Re: Need help in calculating % of total by splitting the values for one of the dimension value for other 2
Ashish Chaudhari Mar 8, 2017 4:17 AM (in response to Simon Runc)I don't have separate column as per excel. Forgot to add this. Your raw sample looks perfect.

6. Re: Need help in calculating % of total by splitting the values for one of the dimension value for other 2
Simon Runc Mar 8, 2017 4:25 AM (in response to Ashish Chaudhari)1 of 1 people found this helpfulCool....
So one way is to create the 3 separate columns like so (notice these are row level calcs, we will aggregate these row level calcs later to create the final versions)
[A  Value]
IIF([Type] = 'A',[Value],NULL)
[A/B  Value]
IIF([Type] = 'A/B',[Value],NULL)
[B  Value]
IIF([Type] = 'B',[Value],NULL)
Once we have these we can aggregate them into the final calcs we need.
[A %age]
(SUM([A  Value])
+
(SUM([A/B  Value])/2))
/
(SUM([Value]))
[B %age]
(SUM([B  Value])
+
(SUM([A/B  Value])/2))
/
(SUM([Value]))
Hope that does the trick!

7. Re: Need help in calculating % of total by splitting the values for one of the dimension value for other 2
Nikunj Bhardava Mar 8, 2017 4:36 AM (in response to Ashish Chaudhari)Hi Ashish  Check attache workbook for your reference and let me know if you have any questions.
Thanks  Nikunj

table.twbx 17.7 KB


8. Re: Need help in calculating % of total by splitting the values for one of the dimension value for other 2
Ashish Chaudhari Mar 8, 2017 4:45 AM (in response to Simon Runc)Hi Simon,
This is working smooth. I have another column called manufacturer. what should I do so that this values will be shown at the manufacturer level. So for a quarter sum of manufacturer will be 100%. or do I need to write different calcs for manufacturer?
Any idea about that?
Regards,
Ashish C.

9. Re: Need help in calculating % of total by splitting the values for one of the dimension value for other 2
Simon Runc Mar 8, 2017 5:00 AM (in response to Ashish Chaudhari)Great news...
Is Manufacturer just a dimension, like Quarter? if so just dragging it in the Viz should be fine. As we're calculating the "new value columns" at row level and then aggregating the results, it should work for any level in the Viz (unless there is a specific different treatment needed for Manufacturer?). This is similar to, say, SUM([Sales]) in superstore, if I bring in Region I get the SUM of Sales by Region, if I then bring in State, I get the SUM of Sales aggregated by Region/State...
If you can post some dummy data, in the structure you have I'll be able to give you a more exact answer.

10. Re: Need help in calculating % of total by splitting the values for one of the dimension value for other 2
Vibhu S Mar 8, 2017 5:14 AM (in response to Simon Runc)Hi,
[Value A]=Sum(if [Category]='A' then [Value] END)
[Value A/B]=Sum(if [Category]='A/B' then [Value] END)
[Value B]=Sum(if [Category]='B' then [Value] END)
[GT]={Fixed [Quarters of Date]: [Value A]}+{Fixed [Quarters of Date]: [Value A/B]}+{Fixed [Quarters of Date]: [Value B]}
[NVA]={Fixed [Quarters of Date]: [Value A/B]}/2)+{Fixed [Quarters of Date]: [Value A]}
[A%]=[NVA]/[GT]*100

11. Re: Need help in calculating % of total by splitting the values for one of the dimension value for other 2
Ashish Chaudhari Mar 8, 2017 5:56 AM (in response to Simon Runc)Hi Simon,
I was able to do it with trial and error method. I will post the sample workbook for sure which will help all of us to discuss it further and many ways in which we can get the answer for the same. i did tried dragging the MF Dim to the color but since for categor A/B there were only 2 MF and for category A there were 5 (Overlapping MF's = 2) values for other 3 were coming as null.
I am sure, I have not used the best method. To get the better understanding of the same. I will upload the same.
Regards,
Ashish C.

12. Re: Need help in calculating % of total by splitting the values for one of the dimension value for other 2
Simon Runc Mar 8, 2017 6:02 AM (in response to Ashish Chaudhari)Coolio!...so it might just be a case of switching out NULLs in the formulas for Zeros (when just using SUM...etc. it doesn't really matter which, but I tend to default to NULL as it means AVG, COUNTD...etc. also work). So either you can change the formulas to
IIF([Type] = 'A',[Value],0)
or wrap the aggregated versions in ZN
[B %age]
(SUM(ZN([B  Value]))
+
(SUM(ZN([A/B  Value]))/2))
/
(SUM(ZN([Value])))
which will handle where MF/Quarter combinations are zero (in our current version if any of the SUM, at whatever level, A, A/B, or B are NULL it will NULL out the final formula)
but yes, let's take a look at the workbook...