Forum Discussion

jkbyerly's avatar
jkbyerly
New Member
9 years ago
Solved

Trouble comparing values in the same column

My data source has the first three columns shown below.  I can't figure out how to create the two columns on the right.  Essentially what I'm trying to do is create a price ratio for each month.  'Standard' is the baseline that other prices should be compared to.  Any ideas?

 

MonthGroupPriceStandard for the month% to Standard
JanBasic11.2580%
JanStandard1.251.25100%
JanPremium1.51.25120%
JanSpecialty1.751.25140%
FebBasic0.951.2874%
FebStandard1.281.28100%
FebPremium1.541.28120%
FebSpecialty1.81.28141%
MarBasic1.021.378%
MarStandard1.31.3100%
MarPremium1.581.3122%
MarSpecialty1.821.3140%
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    jkbyerly

     

    Hi, create the calculated columns:

     

    1:  Standard Column

     

    Standard =
CALCULATE (
    VALUES ( Table1[Price] ),
    FILTER ( ALLEXCEPT ( Table1, Table1[Month] ), Table1[Group] = "Standard" )
)

    2. % to Standard Column

     

    % To Standard = Divide(Table1[Price],Table1[Standard])

    Ready.

2 Replies

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    9 years ago

    jkbyerly

     

    Hi, create the calculated columns:

     

    1:  Standard Column

     

    Standard =
CALCULATE (
    VALUES ( Table1[Price] ),
    FILTER ( ALLEXCEPT ( Table1, Table1[Month] ), Table1[Group] = "Standard" )
)

    2. % to Standard Column

     

    % To Standard = Divide(Table1[Price],Table1[Standard])

    Ready.

    • jkbyerly's avatar
      jkbyerly
      New Member
      9 years ago

      You are a genius!!  Thanks so much for the quick and helpful reply.