1 Reply Latest reply on Dec 29, 2016 9:11 AM by Joe Oppelt

# Cross tab column alignment

Hello All,

I have struggle in implementing below requirement. Could you please anybody help me.

I have below dimensions

Wholesaler

Date

Customer ID

Region

Measures

Total Customers:     CountD(Customer ID)

Total IND Customers:         CountD(IFF([Region]='IND',[Customer ID],NULL))

Total UK Customers:          CountD(IFF([Region]='UK',[Customer ID],NULL))

%IND Customers:     I need to show [Total IND Customers] / [current month IND customers] --- Need help on this formula

%UK Customers:     I need to show [Total IND Customers] / [current month UK customers] --- Need help on this formula

I need to show data in this fashion but I am not able to achieve this. Kindly help me in this requirement. and also help me in Grand totals.

10/201611/201612/201610/2016
11/201612/2016
WholesalersTotal CustomersTotal IND CustomersTotal UK Customers%IND Customers%UK CustomersIND CustomersIND CustomersIND CustomersUK CustomersUK CustomersUK Customers
Tesco100002000800020%80%150019002000750065008000
Walmart2000070001300030%70%50006000700010001000013000
Grand Totals3000090002100065007900900085001650021000

Suresh

• ###### 1. Re: Cross tab column alignment

Tableau won't let you insert things in the middle of a crosstab like Excel will.  the two packages are structured differently.  Tableau is more dimensional-grid oriented.

You can make the calcs you need % IND/UK numbers.  Use LODs to get the total sum of IND and total sum UK.  The specific syntax will depend on things you have set up in your sheet so I can't give you an exact calc, but I bet it will look something like this:

{ EXCLUDE [month] : COUNTD( IF [Region] = "IND" then [Customer ID] END ) }

Essentially this says to get the countD regardless of month.

Your [Total IND Customers] that you showed above will get you the COUNTD by month (if you have months on the columns shelf.)

Then you can do [your LOD Calc] /  [Total IND Customers]

and that will give you the % you are looking for.

How, exactly, you will display that is your next step.

Can you upload a packaged workbook and your tableau version?  We can play with it.