Forum Discussion

pbi_newie's avatar
pbi_newie
Frequent Visitor
3 years ago

Creating variance columns based on dynamic month and scenario selection in a matrix

Hi All,

 

I am trying to create variance columns in a matrix table where the columns could have dynamic data selected form slicers e.g. scenario could be actual, budget, outlook which is is then shown by period i.e. months selected from the slicer.

 

I'd like variance columns to the right based on selections made. My table currently looks as follows:

 

 

My data is coming from a database on a server (which i do not control).

 

Any help would be much appreciated.

 

Thanks!

 

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    pbi_newie , a simple measure should do. if [Budget] ,and [Actual] are measures, a new measure

     

    Variance = [Budget] - [Actual]

    • pbi_newie's avatar
      pbi_newie
      Frequent Visitor
      3 years ago

      Thanks 

       

      Budget and Actuals are not different columns. There is only one column for Amounts. So basically my dataset looks something like the below:

       

       

      I tried creating a new measure as follows but it doesn't work as i'm not able to add it to the existing matrix.

       

      Variance =
      VAR Actuals = CALCULATE(Table[Amounts], Table[Scenario]="Actuals")
      VAR Budget = CALCULATE(Table[Amounts],Table[Scenario]="Budget")
      RETURN
      Budget-Actuals