cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper I

## Change between groups that are not a date

Hello,

I have data from 3 separate studies.  I have combined the data into one table with a column marking each row as coming from Study A, Study B, or Study C.  I can show visuals or grids that separate my measures/data by Study easily however I would like to calculate the differences in different aggregates (study over study growth) between Study C and Study B, and Study B and Study A.  Is there a way to do this?  I know how to do this with time functions (sameperiodlastyear), but not a column that segregates data by text value.

If anyone has any ideas I'd appreciate the help,

Sharon

1 ACCEPTED SOLUTION
Employee

@SharonHMA

In this scenario, to do a "Study over Study" comparison, you can create a numeric column marking different studies like 1,2,3,etc. Then you can create a calculated column to get the Previous Study Total.

`Previous Study Total = CALCULATE(SUM(Table2[Value]),FILTER(Table2,Table2[study number]=EARLIER(Table2[study number])-1))`

Then you can create a measure to calculate the growth percentage.

`Study Over Study Growth = AVERAGE(Table2[Previous Study Total])/CALCULATE(SUM(Table2[Value]))`

Regards,

4 REPLIES 4
Employee

@SharonHMA

In this scenario, to do a "Study over Study" comparison, you can create a numeric column marking different studies like 1,2,3,etc. Then you can create a calculated column to get the Previous Study Total.

`Previous Study Total = CALCULATE(SUM(Table2[Value]),FILTER(Table2,Table2[study number]=EARLIER(Table2[study number])-1))`

Then you can create a measure to calculate the growth percentage.

`Study Over Study Growth = AVERAGE(Table2[Previous Study Total])/CALCULATE(SUM(Table2[Value]))`

Regards,

Helper I

Hi Simon

Do you know why I am receiving this error?  My study number is number, it's basically 1,2 or 3 from a conditional column based on study ie Study = A, then 1 etc.

error says EARLIER/EARLIEST refers to an earlier row context which doesn't exist.  I can't seem to enter any columns after EARLIER.

Helper I

Thank you!  This is the better solution as I add studies down the road, I can increase the numeric values to match.  I appreciate the direction. So easy once you see it.

Employee
Create sum measures for each study:

CALCULATE(SUM(Table[FieldToSum]),FILTER(Table,Table[Study] = "A"))

Then create measures for growth (measureSumA/measureSumB - 1)

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors