12 Replies Latest reply on Mar 8, 2017 6:02 AM by Simon Runc

# Need help in calculating % of total by splitting the values for one of the dimension value for other 2

Hi All,

Please find the query below. Also attaching the excel file for the same.

Regards,

Ashish C.

• ###### 1. Re: Need help in calculating % of total by splitting the values for one of the dimension value for other 2

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
A100
A/B20
B

130

A200
A/B50
......

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

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

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

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

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

Cool....

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!

1 of 1 people found this helpful
• ###### 7. Re: Need help in calculating % of total by splitting the values for one of the dimension value for other 2

Hi Ashish - Check attache workbook for your reference and let me know if you have any questions.

Thanks - Nikunj

1 of 1 people found this helpful
• ###### 8. Re: Need help in calculating % of total by splitting the values for one of the dimension value for other 2

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?

Regards,

Ashish C.

• ###### 9. Re: Need help in calculating % of total by splitting the values for one of the dimension value for other 2

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

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

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

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...