Forum Discussion

Drogov4122's avatar
Drogov4122
Regular Visitor
3 years ago

Calculating Variance between two categories and month

Hello,

I am seeking to find the fuel price (last column) variance between DOM and FTZ (third column) for each month and year. I have tried a variety of calculations, including the Quick Measure feature, however, am still struggling to find the proper solution. My apologizes if this is an easy fix as I am still new to PowerBi.

 

Clarification: All the data below lives in one table titled "WIP Table"

 

Thank You!

 

variance

3 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi Drogov4122 

    Please try

    Variance =
CALCULATE ( [Avg Fuel Price], 'Table'[DOM/FTZ] = "DOM" )
    - CALCULATE ( [Avg Fuel Price], 'Table'[DOM/FTZ] = "FTZ" )
    • Drogov4122's avatar
      Drogov4122
      Regular Visitor
      3 years ago

      Does not seem to work  - I am guessing because "Avg Fuel Price" is also part of the same table (i.e: DOM/FTZ, MONTH, YEAR and AVG FUEL PRICE are all part of "WIP Table"

      • tamerj1's avatar
        tamerj1
        Community Champion
        3 years ago

        Drogov4122 

        Then try

        Variance =
CALCULATE ( AVERAGE ( 'WIP Table'[Avg Fuel Price] ), 'WIP Table'[DOM/FTZ] = "DOM" )
    - CALCULATE ( AVERAGE ( [Avg Fuel Price] ), 'WIP Table'[DOM/FTZ] = "FTZ" )