Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jkbyerly
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?

 

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%
1 ACCEPTED SOLUTION
Vvelarde
Community Champion
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])

Ready.




Lima - Peru

View solution in original post

2 REPLIES 2
Vvelarde
Community Champion
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])

Ready.




Lima - Peru

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.