Do calculation on cell level

[hokitkan]

Hello everyone! I'm new to power BI and now I'm facing some problems about calculating on cell level.

I've got some data about the growth rate of different kinds of virus. 

Here is the Sample file

The growth rate was recorded automatically whenever its change. Therefore, the growth rate between to time points is assume the same. 

What I want to do is to compare the growth rate of the virus to the previous average growth rate at each time point, like the following picture:

And the desire outcome should be like this:

I'm really struggle on this problem and I don't even know if it's possible to do this on Power BI.

Any hints or advises would be much appreciated!

[Anonymous · Accepted solution]

Well, the good news is that it is certainly possible, if a bit tricky 🙂

You are looking for... whatever value is stored at... the max time that is LESS THAN the current time.

May be easier to break this into 2 columns, but ... we are feeling lucky.  I would try:

Prior A Value =
     VAR MyTime = Trials[Time]
     VAR MyVirus = Trials[Virus]
     VAR PriorTime = CALCULATE(MAX(Trials[Time]), FILTER(ALL(Trials), Trials[Time] < MyTime && Trials[Virus] = MyVirus))
     RETURN CALCUALTE(VALUES(Trials[Average Growth Rate]), FILTER(ALL(Trials), Trials[Time] = PriorTime && Trials[Virus] = MyVirus))

[hokitkan]

Thanks for your reply and really impressed by your idea!

With a little amend, your formula helps me to obtain the Average Growth Rate from previous time point!

Now I'm using the following formula:

VAR PriorTime = CALCULATE(MAX('Trials'[Time]), FILTER(ALLEXCEPT('Trials','Trials'[Trial]), 'Trials'[Time] < EARLIER('Trials'[Time])))

RETURN
CALCULATE(VALUES('Trials'[Average Growth Rate (AGR)]), FILTER(ALLEXCEPT('Trials','Trials'[Trial]), 'Trials'[Time] = PriorTime))

And I got this:

But I still can't get the desire outcome,as I can't divide the Growth Rate by Previous Average Growth Rate by the following formula:

VAR PriorTime = CALCULATE(MAX('Trials'[Time]), FILTER(ALLEXCEPT('Trials','Trials'[Trial]), 'Trials'[Time] < EARLIER('Trials'[Time])))
RETURN
CALCULATE(VALUES('Trials'[Growth Rate]), FILTER(ALLEXCEPT('Trials','Trials'[Trial],'Trials'[Virus]), 'Trials'[Time] <= EARLIER('Trials'[Time])))/
CALCULATE(VALUES('Trials'[Average Growth Rate (AGR)]), FILTER(ALLEXCEPT('Trials','Trials'[Trial]), 'Trials'[Time] = PriorTime))

Error: A table of multiple values was supplied where a single value was expected

How can I calculate the result at each time point by assuming the reading keep the same as the previous reading untill the next reading occur? So I can got this:

 Again, thanks for your reply and I really need your help!

[hokitkan]

Update:

I can divide the Growth Rate by Previous Average Growth Rate by the following formula:

VAR PriorTime = CALCULATE(MAX('Trial'[Time]), FILTER(ALLEXCEPT('Trial','Trial'[Trial]), 'Trial'[Time] < EARLIER('Trial'[Time])))
VAR CurrentTime = CALCULATE(MAX('Trial'[Time]), FILTER(ALLEXCEPT('Trial','Trial'[Trial]), 'Trial'[Time] = EARLIER('Trial'[Time])))
     RETURN
CALCULATE(VALUES('Trial'[Growth Rate]), FILTER(ALLEXCEPT('Trial','Trial'[Trial],'Trial'[Virus]), 'Trial'[Time] = CurrentTime))/
CALCULATE(VALUES('Trial'[Average Growth Rate (AGR)]), FILTER(ALLEXCEPT('Trial','Trial'[Trial]), 'Trial'[Time] = PriorTime))

And then I can creat a new table with the following formula:

SUMMARIZE('Trial','Trial'[Trial],'Trial'[Virus],'Trial'[% of AGR],'Trial'[Time])

And I almost got the desire result like this:

However, I can't get the data of virus B at each time point (Assume the readings of virus B at 12:00 and 13:00 time point keep the same as 9:00). I want to know if it's possible to get the extra row of data in power bi desktop?

[hokitkan]

I've created another post talking about this problem and it was tackled by v-qiuyu-MSFT.

http://community.powerbi.com/t5/Desktop/Generate-missing-data-at-certain-time-points/m-p/198567