Forum Discussion

hokitkan's avatar
hokitkan
Frequent Visitor
9 years ago
Solved

Do calculation on cell level

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 ...
General Comment
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    9 years ago

    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's avatar
hokitkan
Frequent Visitor
9 years ago

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's avatar
hokitkan
Frequent Visitor
9 years ago

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?