14 Replies Latest reply on May 17, 2018 1:52 PM by NAYANA MADHEKAR

# Calculations

Hello there!

I have a tricky one this time.  ( I think so)

I have number of shops and their respective Sales.

Shop1 = 100

Shop 1=145

.

.

.

.

Shop 10 = 456

Shop 11= 1700

But the Shop 11 is an online Store.. Therefore want to distribute that shop sales into the other stores according to their percentage of Total.

Example: I have Percent of Total for all shops (1-10) except Shop 11.

Now if Percent of Total for Shop1 is 3.03%

Then the Sales of Shop 11 should be distributed as follows:  3.03% of 1700( Shop 11)  should be added under Shop1

4.39 of 1700 should be added under Shop2

5.33% of 1700 should be added under Shop3 & etc

Is there a way we could accomplish this in Tableau? Please let me know if any questions.

Desired output:

Fruits          Sales

Shop 1:      151.51

Shop2:        219.63

.

.

.

Shop 10: 690.77

Thank you.

• ###### 1. Re: Calculations

Hi Nayana,

Hope these helps.

It might be over complicated with the consideration of matching "100%" after ratio multiplication.

If you don't care decimal points, "Shop10" special handling is not needed.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 2. Re: Calculations

Thank you very much.  I highly appreciate your time & help.

• ###### 3. Re: Calculations

HI Nayana

You are welcome.

Thanks,

Shin

• ###### 4. Re: Calculations

Hello Shinichiro,

the data you helped me was a dummy data.. In my original dataset .. When calculating the sum :

This works : If [Online/Real]="Real" THEN SUM([Hours Worked]) ELSE 0 END

But if i try putting a FIXED in this calculation like below:

{ FIXED :(IF [Online/Real]="Real" THEN SUM([Hours Worked]) ELSE 0 END)}

It gives me an error.

Error:  All fields in a level of detailed expression must come from the same datasource.

Could you help?

Thanks a ton!

• ###### 5. Re: Calculations

Without seeing data, but from the logic, try below, but again, difficult to say without data.

{ FIXED : sum (IF [Online/Real]="Real" THEN [Hours Worked] ELSE 0 END)}

Thanks,

Shin

• ###### 6. Re: Calculations

I get an error:

Cannot mix aggregate & non aggregate comparisons or results in 'IF' expressions.

• ###### 7. Re: Calculations

Please attach sample data as twbx with having error.

Thanks

Shin

• ###### 8. Re: Calculations

Hi Shinichiro,

Thank you for these quick responses..

As per our discussion above..

I have created a sample data with the same error.

Need to fix the calculation for FIXED CALCULATION.

Thank you.

• ###### 9. Re: Calculations

Here it is..

• ###### 10. Re: Calculations

Hi Shinichiro,

Unfortunately I cannot use JOIN.. The original dataset I have is huge & has been blended.. cannot make those changes.

But here are the excel sheets.. I agree JOIN would help.. but cannot use it for now.

• ###### 11. Re: Calculations

You need to use join instead of blend,

Please correct "Shop 11" with "Shop11" or vise-versa.  (space in between or not)

Or

Rest of them are same.

Thanks,

Shin

• ###### 12. Re: Calculations

I cannot use JOIN. This is just a dummy data. My orginal data is all blended & cannot be changed.

Please let me know if you could still help over the FIXED Calculation.

All your help is highly appreciated.

Thank you.

• ###### 13. Re: Calculations

Unfortunately, Fixed does not work on blend.

Shin

• ###### 14. Re: Calculations

Np, Thanks Shinichiro!