locked
Cross Table Measure RRS feed

  • 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 Uziel9999 Wednesday, October 12, 2016 4:49 PM
    Wednesday, October 12, 2016 4:45 PM

Answers

  • 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