Create a Pivot Table by association over multiple dimensions

[kushagra_jain]

I have to create a report to measure the Client Satisfaction Index (CSI) for each month region wise. which is calculated using the below formula:

  1 - (complaints received for each month for each region / Inquiry received for each month in each region)

I get the data from these two table:

1) Complaint_table: 

2) Inquiry_table:

Now I have associated these two tables on Year_month. I am stuck after this as I also want to associate over Region as otherwise how will I map the same two regions with each other.

Finally I want to create a pivot table with region as rows and columns as Year month and the measure as the CSI formula described above.

For e.g

                  Sep-18          Oct-18 

Region       CSI                  CSI

A                 40%                 50%

B                 50%                 67%

C                  67%                0%

D                 67%                 67%

E                  33%                 50%

I am not sure how to do this analysis, Please help me out.

Thank You

[Lisa_P · Accepted solution]

Hi,

I've done this by loading all the data into one table and categorizing the data as either a complaint or an inquiry in a new field, Type.
LOAD
Date([Complaint_Year Month],'MMM-YY') as YearMonth,
[Complaint_Region] as Region,
[Complaint_Name] as Name,
[Complaint_Service] as Service,
'Complaint' AS [Type]
FROM [lib://Community/Example_data_source.xlsx]
(ooxml, embedded labels, table is Sheet1);

Join
LOAD
Date([Inq_Year Month], 'MMM-YY') as YearMonth,
[Inq_Region] as Region,
[Inq_Name] as Name,
[Inq_Service] as Service,
'Inquiry' AS [Type]
FROM [lib://Community/Example_data_source.xlsx]
(ooxml, embedded labels, table is Sheet2);

Then in the pivot table use this expression to determine the CSI:
Count({<Type={Complaint}>}Name)/count({<Type={Inquiry}>}Name)

[jonathandienst]

I agree with Lisa_P, that's what I would do too.