Forum Discussion

erihsehc's avatar
erihsehc
Helper III
8 years ago
Solved

variance against target calculation

hi there,

 

For the below screenshot, I would like to create a column for target%GP and one column for GP% variance against target on matrix. I have no idea how to create it (I can not directly drag the targetGP% column to matrix), would you help on that? thanks

 

Data model is as below

 data model

 

Best regards,

ER

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi erihsehc,

     

    Based on my test, your should be able to follow steps below to get your expected result.

     

    1. Set the Cross filter direction of the relationship the two tables to Both.

     

     

    2. Then should be able to use the formulas below to create two measures to calculate target%GP and the variance, and show them on the Matrix.

    Target GP% = MAX(Target[targetGP%])
    Variance = [GP%] - [Target GP%]

     

    Here is the modified sample pbix file for your reference. :smileyhappy:

     

    Regards

2 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    8 years ago

    Hi erihsehc,

     

    Based on my test, your should be able to follow steps below to get your expected result.

     

    1. Set the Cross filter direction of the relationship the two tables to Both.

     

     

    2. Then should be able to use the formulas below to create two measures to calculate target%GP and the variance, and show them on the Matrix.

    Target GP% = MAX(Target[targetGP%])
    Variance = [GP%] - [Target GP%]

     

    Here is the modified sample pbix file for your reference. :smileyhappy:

     

    Regards