# Cross Table Measure

• ### Question

• Hello,

Below is the Excel 2016 data model on which I am working.  The four tables on the right are lookup tables.  Table tDetailCC is actual GL data.  tFCCC is total forecast and tPlan is a subset containing data at the Purchase Order (PO) level.

Everything works.  However, I am trying to create a True/False column to identify which lines have a variance, which will help minimize screen usage to the relevant data. How do I create a column that will calculate based on the context the user selects in the pivot?

The measures in question are:

• tDetailCC[\$ CC]- Actuals
• tPlan[\$ Plan]- Forecast data
• tPlan[\$ Plan Var]:=var x=IF( NOT HASONEVALUE( tPO[?Has PO] ), [\$ CC FC], SWITCH( VALUES( tPO[?Has PO] ), TRUE(), SUM( tPlan[Amount] ), FALSE(), [\$ CC FC] - CALCULATE( SUM( tPlan[Amount] ), ALL( tPO ), tPO[?Has PO] ), BLANK() ) ) return if(x,x)    (essentially, tDetailCC[\$ CC] - tPlan[\$ Plan])

• Edited by Wednesday, October 12, 2016 4:49 PM
Wednesday, October 12, 2016 4:45 PM

• Hi Uziel9999,

Based on your description, you am trying to create a True/False column to identify which lines have a variance. The lines means rows or columns? If you want to compare current row with previous row, you can refer to the following steps. Create a Rank column and filter table context with this Rank column.

``` Rank =RANKX(Table1,Table1[ID],,1)

Previous = LOOKUPVALUE('Table1'[sale],Table1[RANK],Table1[RANK]-1)

Column = IF('Table1'[Previous]=Table1[sale],"True","False")```

If this is not what you want, could you please share more details and screenshot for further analysis?

Best Regards,
Angelia
Thursday, October 13, 2016 12:43 PM