cancel
Showing results for
Did you mean:
New Member

## 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?

 Month Group Price Standard for the month % to Standard Jan Basic 1 1.25 80% Jan Standard 1.25 1.25 100% Jan Premium 1.5 1.25 120% Jan Specialty 1.75 1.25 140% Feb Basic 0.95 1.28 74% Feb Standard 1.28 1.28 100% Feb Premium 1.54 1.28 120% Feb Specialty 1.8 1.28 141% Mar Basic 1.02 1.3 78% Mar Standard 1.3 1.3 100% Mar Premium 1.58 1.3 122% Mar Specialty 1.82 1.3 140%
1 ACCEPTED SOLUTION
Community Champion

@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])`

Lima - Peru
2 REPLIES 2
Community Champion

@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])`

Lima - Peru
New Member

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

Announcements

#### Launching new user group features

Learn how to create your own user groups today!