I have a pivot table that looks at 3 different measures from different data sources and compares the amounts from Calendar Year to Fiscal Year.
The attached image shows how the pivot table is currently set up.
The column is an expression using multiple if statements like: (dual function helps me manually sort the columns in our required order). The first column(s) is splitting the amounts by a fiscal year dimension from load script.
The 2nd and 3rd columns are manual titles to label them as.
=if(SOURCE='Src1', [Fiscal Year],
if(SOURCE='Src2, dual('Seperate Measure 1',2),
if(SOURCE='Src3',dual('Seperate Measure 2',3))))
The measure expression is also set up using multiple if statements like:
=if(SOURCE='Src1', Sum([Measure 1]/1000)
if(SOURCE='Src2, Sum([SepMeasure_1]/1000),
if(SOURCE='Src3, Sum([SepMeasure_2] /1000))))
I am trying to set up a totals column of Src1 (Measure 1) so it looks like it's adding up all the horizontal rows for Measure 1. However I can't seem to find the right syntax to do so. Could anyone help with this situation or suggest new redesign ideas.