Yes I'm able to now.
Thanks! and will study this.
I've looked at the dataset and I'm confused how the Exec PnL Header in the viz contains COGS, TOTAL SALES, GROSS MARGIN, INDIRECT EXPENSES, OPERATING INCOME AND NET INCOME (LOSS) when that column in the data doesn't list any of those named dimensions. It is in the grouped Exec PnL Header but the Viz doesn't have that pulled into the "Rows".
The actual datasource in my workbook consists of
the two tables from the two different connections (Excel files).
1. The first table called Sheet1 has 14 rows, and it is coming
from the BPC_test_without_empty_lines.xlsx file.
It does contain the [Exec P&L Heading] Dimension --
with 14 unique values built in the 14 Marks on the view.
2. The second table contains the actual P&L data,
and it is Left Joined to the first one ON the [Account] column.
The [Amount] column from this (second) table got aggregated
into the corresponding 'Leaf' values of the [Account] dimension,
such as 'Third Party Sales', 'Material Cost', 'Advertising & Promotion' etc.
All other 'TOTAL' Amounts are calculated via LOD expressions.
Thank you for the detailed response Yuri. After I posted, I realized that the Exec P&L Heading column contained the 'TOTAL' values and I couldn't see them until I filtered them out on the Data Source tab.
I have used this with my data source and the 'Totals' do not equal the LOD that I am calculating. Granted, my data is more complex as I have years and scenarios (Actual, Proforma, etc.) to add. I think I will open another thread to ask for help if I still have problems.