Multiple Lookup Column Math

[rmacy]

Hi,

I was hoping to get some help with some data we are trying to plot in PowerBI. We have a table of data similar to what is shown below. At Time = 0 we know that the value in the mass column is the initial mass of our samples. We want to create a mass loss column so that we can plot mass loss vs. time for each sample. This would mean each new sample would start with a mass loss of 0 and then go up by the difference.

Is there a single function that can do this, or will this require the use of an intial mass column followed by a total mass loss column? In both cases, what would be the best way to go about doing this?

Sample	Time (s)	Mass (g)
1	0	10
1	30	9.9
1	60	9.8
1	120	9.7
1	180	9.5
2	0	15
2	15	14.9
2	60	14.7
2	120	14.5

Thanks in advance!

[parry2k]

@Fowmy with that assumption it is fine but if there ever a case it can go on the higher side, logic will fail, to be on the safer side, if I have to solve for it, I will use initial value based on time (s) = 0 and then it doesn't matter if value depletes or increase over time. Just my 2 cents.

[parry2k]

@Fowmy great solution but see one drawback if initial mass where time(s) = 0 is smaller than other mass (g) values for the same sample, your calculation will not work, it will take the maximum value of that sample not the initial time (s) = 0 value, and I think that is one of the requirement, always look at the initial value.

Just my 2 cents. I could be totally wrong here.

[Fowmy]

@parry2k 

Thanks for the observation, I assumed the initial mass keeps depleting as time passes. 

[Fowmy]

@rmacy 

I am not sure if you need the accumulated mass difference or individual difference. Below column shows the cumulative:

Mass Loss = 
var __currsample = Table7[Sample]
var __currmass = Table7[Mass (g)]
var __inimass =
    MAXX( FILTER( Table7 , Table7[Sample] = __currsample &&   Table7[Time (s)] = 0), Table7[Mass (g)] )
return
    __inimass - __currmass

If you are interested in the individual difference, take the one below:

Mass Loss Indv = 
    var __currsample = Table7[Sample]
    var __currmass = Table7[Mass (g)]
    var __currtime = Table7[Time (s)]
    var __prevtime =
        MAXX( FILTER( Table7 , Table7[Sample] = __currsample && Table7[Time (s)] < __currtime), Table7[Time (s)] )
    var __premas =
        MAXX( FILTER( Table7 , Table7[Sample] = __currsample && Table7[Time (s)] = __prevtime), Table7[Mass (g)] )
    return
      __premas - __currmass