Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

calculate variance on matrix table

thats a matrix table. how do i cal variance between budget vs fullyallocated columns? 

Need Help
Show and Tell

2 Replies

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Anonymous ,

     

    You may try the measure below.

    Measure =
IF (
    ISINSCOPE ( Table1[Column2] ),
    SUM ( Table1[Value] ),
    CALCULATE ( SUM ( Table1[Value] ), Table1[Column2] = "budget" )
        - CALCULATE ( SUM ( Table1[Value] ), Table1[Column2] = "fullyallocated" )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      no calculating  as expected. but the bigger problem is i need to just get one colum, but i get 2 colums for that measure. i know its bcuase of matrix any way to restric it ? s

      Measure = 
IF (
    ISINSCOPE (HL_scenario_Prim[lvl1] ),
    SUM ( 'Fact'[Finance_Value] ),
    CALCULATE ( SUM ('Fact'[Finance_Value] ),HL_scenario_Prim[lvl1] = "BUDGET" )
        - CALCULATE ( SUM ( 'Fact'[Finance_Value] ), HL_scenario_Prim[lvl1] = "FullyAllocatedActuals" )
)