I have a 2 dimension tables which I would like to join so that selections can be made to filter results across all sheets on the app.
Table 1 is a basic dimension table containing account information including account category
Table 2 contains account categories, as well as some additional records such as GM (Gross Margin) and GM% (Gross Margin%), and other records which are used to define "Totals" rows within the dimension. This is an inline table.
I need table 2 as i'm unable to insert the "Totals" records within table 1 as a category whilst being able to calculate that total within the table.
I then have 2 sheets, Sheet 1 showing monthly figures broken down by account. Sheet 2 showing the totals of those accounts by category, as well as Gross Margin calculations etc...
The figures shown in Sheet 2 are calculated within the measure expression using Pick, 1 expresssion per record for table 2.
I am trying to join the two tables so that when selections are made on Table 1 or Table 2, the selection will be made across the whole sheet, whilst also keeping the "Gross Margin" records from Table 2 visible on Sheet 2 when no selection is made on Sheet 1.
The problem i'm facing is that when joining Table 2 to Table 1, only the categories are displayed on Sheet 2 and not the totals rows.
Is there a way to join the 2 tables to enable selection functionality, whilst not omitting any records from Table 2?
Examples:
[Table1]:
Load
[GL Account],
[Category]
From [Table1Source];
[Table2]:
Load * Inline [
Category, CatSeqNo
Invoiced Sales, 1
POC Adjustment, 2
COS, 3
GM1, 4
GM1%, 5
];
Example of expressions within Sheet2:
(The sums are managed through master measures and so those measures are referenced within the pick expression, for tidiness)
=pick(CatSeqNo
,InvoicedSales
,POCadjustment
,COS
,GM1
,GM1%)
Many Thanks!
Tom
