Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Solved!
Created a third table containing a "GL Account" field and a "Category" field.
I made sure that the table contained a record for any GL Account that is included in a category total. This meant that all GL accounts appeared many times.
I then joined the GL account to my master dimension table, and the Category field to my inline Category table
Now when I make a category selection on the table within the sheet, that filter is applied to all tables with applicable GL accounts related to that category.
Example below:
GL Account | Total Class |
123456 | Total Invoiced Sales |
234567 | Total Invoiced Sales |
345678 | Total Invoiced Sales |
456789 | Total Invoiced Sales |
567890 | Total Invoiced Sales |
678901 | Total Invoiced Sales |
789012 | Total Invoiced Sales |
890123 | Total Invoiced Sales |
987654 | Total POC Adjustment |
876543 | Total POC Adjustment |
765432 | Total POC Adjustment |
123456 | Total Sales |
234567 | Total Sales |
345678 | Total Sales |
456789 | Total Sales |
567890 | Total Sales |
678901 | Total Sales |
789012 | Total Sales |
890123 | Total Sales |
987654 | Total Sales |
876543 | Total Sales |
765432 | Total Sales |
654321 | Total COS |
543210 | Total COS |
432109 | Total COS |
321098 | Total COS |
210987 | Total COS |
123456 | GM1 |
234567 | GM1 |
345678 | GM1 |
456789 | GM1 |
567890 | GM1 |
678901 | GM1 |
789012 | GM1 |
890123 | GM1 |
987654 | GM1 |
876543 | GM1 |
765432 | GM1 |
654321 | GM1 |
543210 | GM1 |
432109 | GM1 |
321098 | GM1 |
210987 | GM1 |
Solved!
Created a third table containing a "GL Account" field and a "Category" field.
I made sure that the table contained a record for any GL Account that is included in a category total. This meant that all GL accounts appeared many times.
I then joined the GL account to my master dimension table, and the Category field to my inline Category table
Now when I make a category selection on the table within the sheet, that filter is applied to all tables with applicable GL accounts related to that category.
Example below:
GL Account | Total Class |
123456 | Total Invoiced Sales |
234567 | Total Invoiced Sales |
345678 | Total Invoiced Sales |
456789 | Total Invoiced Sales |
567890 | Total Invoiced Sales |
678901 | Total Invoiced Sales |
789012 | Total Invoiced Sales |
890123 | Total Invoiced Sales |
987654 | Total POC Adjustment |
876543 | Total POC Adjustment |
765432 | Total POC Adjustment |
123456 | Total Sales |
234567 | Total Sales |
345678 | Total Sales |
456789 | Total Sales |
567890 | Total Sales |
678901 | Total Sales |
789012 | Total Sales |
890123 | Total Sales |
987654 | Total Sales |
876543 | Total Sales |
765432 | Total Sales |
654321 | Total COS |
543210 | Total COS |
432109 | Total COS |
321098 | Total COS |
210987 | Total COS |
123456 | GM1 |
234567 | GM1 |
345678 | GM1 |
456789 | GM1 |
567890 | GM1 |
678901 | GM1 |
789012 | GM1 |
890123 | GM1 |
987654 | GM1 |
876543 | GM1 |
765432 | GM1 |
654321 | GM1 |
543210 | GM1 |
432109 | GM1 |
321098 | GM1 |
210987 | GM1 |