1 2 3 Previous Next 38 Replies Latest reply on Apr 28, 2016 10:33 PM by Shivang Desai

# Getting total sales in tableau for all order numbers that have order number same as beverages

Hi All,

I am a bit stuck in getting a particular scenario for Tableau.

I am trying to get total sales for a particular sales order which includes only beverages.

Example: Salesorder#1: Beverages - 5\$

Chicken - 10\$

Salesorder#2: Chicken - 12\$

Chips - 8\$

Salesorder#3: Deserts - 4\$

Beverages - 6\$

I just need sum of total which has beverages in it. So, as per above examples, I should get Total = 25\$ i.e. (Beverages + Chicken = 15\$) and (Deserts+ Beverages = 10\$) and it should not include the salesorder2.

How can I achieve this in Tableau?

Also an extension top this is getting all by a store name?

Kind Regards

• ###### 1. Re: Getting total sales in tableau for all order numbers that have order number same as beverages

Hi Shivang-

I created two calcs to do this.  You can filter on this calc, I just left it in there for you to see how it worked.

Beverage Isolation

IF [Category] = "Beverages" THEN 1 ELSE 2 END

Order Contain Beverages?

IF {fixed [Sales Order] : MIN([Beverage Isolation])} = 1 THEN "Yes" ELSE "No" END

9.3 attached.

• ###### 2. Re: Getting total sales in tableau for all order numbers that have order number same as beverages

When I create second calculation it says "bad character"  (due to curly braces and colon I believe)

• ###### 3. Re: Getting total sales in tableau for all order numbers that have order number same as beverages

Hi shivang desai,

which version of tableau desktop you are using. The second calculation was done by using LOD expressions which will be supported by the (>x versions. Please make sure that you are using the 9.0 or higher versions.

• ###### 4. Re: Getting total sales in tableau for all order numbers that have order number same as beverages

Hi,

I have Tableau 8.2 version. Now how can I solve this?

• ###### 5. Re: Getting total sales in tableau for all order numbers that have order number same as beverages

Hi Shivang desai,

You are using lower version than 9.0 . So, LODs are not supported in that 8.2 version. if you want to use LOD then you need to upgrade your TD to 9.0 or higher version or else you need to write the calculations manually by breaking down them it into several parts.

• ###### 6. Re: Getting total sales in tableau for all order numbers that have order number same as beverages

Any idea on doing the above?

• ###### 7. Re: Getting total sales in tableau for all order numbers that have order number same as beverages

Hi Shivang

I tried to calculate the things the way you have want but I am stuck at an issue, which is my calculations are giving the proper order ID which contains the beverages. When I use them it only considers the Beverage and ignores the second item from the order. I think it need to be achieved using some restructuring of data.

Guys Please take it forward from here if possible.

Attaching the 9.2 (Tried achieving without LOD)

Truly need some expert advice/guidance. Mahfooj, can this be achieved without using the LOD?

Thanks and Regards,

Ashish Chaudhari

• ###### 8. Re: Getting total sales in tableau for all order numbers that have order number same as beverages

We have to biuld "BRIDGE TABLE" at DB level to implement the N: N Relationship. (Namy to Many Relationship between 2 Tables).

• ###### 9. Re: Getting total sales in tableau for all order numbers that have order number same as beverages

is there any workaround for this in tableau 8.2 version?

I am sure this can be possible without using LOD functions. (tableau must have considered this scenarios before releasing 8.2 version)

Thanks and Regards,

Ashish Chaudhari

• ###### 10. Re: Getting total sales in tableau for all order numbers that have order number same as beverages

Hi Kumar,

I think your suggestion is right. Can you try to demonstrate it? Even I had a say that there is some data restructuring or something at the data level needs to implement which will consider multiple items for one order.

Thanks and Regards,

Ashish Chaudhari

• ###### 11. Re: Getting total sales in tableau for all order numbers that have order number same as beverages

Do you mean to say that need to restructure the database? Or it can be done directly through Tableau?

• ###### 12. Re: Getting total sales in tableau for all order numbers that have order number same as beverages

Books_Table

Book     Author

B1          A1

B1          A2

B1          A3

B2          A1

B3          A2

B4          A3

Sales_Table

T_ID     Book     Sales

T1     B1          100

T2     B2          100

T3     B3          100

T4     B4          100

As per the SalesTable, Total Sales Volume is 400.

But when we connect these two Tables using Books_Table.Book= Sales_Table.Book

Author     Sales

A1          200

A2          200

A3          200

Here we get the Total Sales as 600, but as we known there is Total Sales AMT is 400.

Here 1 Book (B1)  is authored by 3 Authors, so B1 sales AMT is reflecting to 3 Authors.

We can say 1 Book can be written by multiple Authors and 1 Author can write multiple Books. (Many to Many).

Here we have to create a NEW BRIDGE TABLE @ DB Level.

A bridge table
sits between a fact table and a dimension table and is used to resolve
many-to-many relationships between a fact and a dimension.
Bridge
table will contain only two dimension column ,key columns in both dimension
.

Suppose there are two dimensions Customer
and Region,

create table
Customer(Customer_id

int, Name
varchar(110));

create table
Region
(Country_id
int, Desc varchar(50));

Bridge
table is created by joining the two tables Customer and Region using dimension
keys

 create table Brige_table_Custregion (Customer_id int, Country_id int)

Best Regards

Kumar

• ###### 13. Re: Getting total sales in tableau for all order numbers that have order number same as beverages

Trying this out.

Thanks Kumar.

• ###### 14. Re: Getting total sales in tableau for all order numbers that have order number same as beverages

Hi Shivang,

compute the table calculation in filter at cell level.

1 of 1 people found this helpful
1 2 3 Previous Next