Hi,

Is there a way to show a single grand total for the leftmost consolidation in rows in PAW? I can do this without any trouble in Excel Pivot, but I'm finding it tricky in PAW. Does anyone have a simple solution or workaround for this?

Thanks!

------------------------------
Asgeir Thorgeirsson
------------------------------

HI Asgeir,

You could leverage asymmetrical views to give you the Total Asset ID and a SUM total then below that show the N level combinations.

On my Sales model with Products and Customers, it would look like this:

And the MDX code could be something like the below - note the sum is based on the set being used on rows:

WITH 
MEMBER [Customer].[Customer].[SUM] AS 
IIF(
   ISLEAF([Product].[Product].CurrentMember),
	NULL,
	SUM(
		{TM1SubsetToSet([Customer].[Customer],"Leaf Elements","public")}
		)
	), 
	SOLVE_ORDER = 1, FORMAT_STRING = '#,##0;(#,##0)' 

SELECT NON EMPTY {[Period].[Period].MEMBERS} ON 0, 
NON EMPTY 
{
	{[Product].[Product].[Total Products]}*{[Customer].[Customer].[SUM]},
	{DRILLDOWNMEMBER({[Product].[Product].[Total Products]}, {[Product].[Product].[Total Products]})}*{TM1SubsetToSet([Customer].[Customer],"Leaf Elements","public")}
}
ON 1 

FROM [Sales] 
WHERE ([Scenario].[Scenario].[Actual], [Sales Measures].[Sales Measures].[Units])

------------------------------
George Tonkin
Business Partner
MCI Consultants
Johannesburg
------------------------------

Original Message:
Sent: Tue May 07, 2024 10:10 AM
From: Asgeir Thorgeirsson
Subject: PAW Grand Totals

Hi,

Is there a way to show a single grand total for the leftmost consolidation in rows in PAW? I can do this without any trouble in Excel Pivot, but I'm finding it tricky in PAW. Does anyone have a simple solution or workaround for this?

Thanks!

------------------------------
Asgeir Thorgeirsson
------------------------------