Change between groups that are not a date

[SharonHMA]

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

[v-sihou-msft · Accepted solution]

@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,

[SharonHMA]

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.

[SharonHMA]

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.

[dkay84_PowerBI]

Create sum measures for each study:

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

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