Forum Discussion

gonnelli's avatar
gonnelli
Frequent Visitor
7 years ago
Solved

Difference between 2 rows using first row fixed

Hello!   I need to calculate the difference between two rows in a matrix table in Power BI, but I need to keep the first row as reference, like this:   EDIT: The first row (Model "A") can be chan...
General Comment
Need Help
Tips and Tricks
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi gonnelli,

     

    Does that make sense? If so, kindly mark my answer as a solution to close the case.


    Regards,
    Frank

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Have you tried using the LOOKUPVALUE function? You can use this to return the value for Model A in whatever column you need to reference. 

 

In your example below, the formula would look something like (I'm using "Table1" as the table where your values are stored in this example):

 

diff = CALCULATE(AVERAGE(Table1[price])-LOOKUPVALUE(Table1[price],Table1[model],"A"))
 
Put this measure as the diff column in your matrix table visual and you should get the desired results.
gonnelli's avatar
gonnelli
Frequent Visitor
7 years ago

Hello v-frfei-msft

In my case, I can't use lookupvalues because the first "model" can be changed by filters.

 

I need to calculate always the difference between the first row with another rows.

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    Hi gonnelli,

     

    I made an update of the measure.

     

    diffm = MAX(Table1[price])-CALCULATE(SUM(Table1[price]),FILTER(ALLSELECTED(Table1),Table1[model]=MINX(ALLSELECTED(Table1),Table1[model])))

     

    For more details, please check the pbix as attached.

     

    Regards,

    Frank

    • v-frfei-msft's avatar
      v-frfei-msft
      Community Support
      7 years ago

      Hi gonnelli,

       

      Does that make sense? If so, kindly mark my answer as a solution to close the case.


      Regards,
      Frank