Forum Discussion
Calculated column using data from multiple rows and columns
Hi, I've been stuck at a problem for a while now. I checked multiple other questions like mine but I wasn't able to find the right answer so I thought I'd ask. My situation looks like this:
| Value | SubIndex | FrameID | Date | SOLUTION |
| 34 | 1 | THXY22 | 1.1.2020 | (BLANK) |
| 50 | 5 | THXY22 | 1.1.2020 | (BLANK) |
| 55 | 5 | THXY24 | 7.2.2020 | 1,0416... |
| 44 | 1 | THXY24 | 7.2.2020 | 1,0416... |
| 120 | 6 | THXY24 | 7.2.2020 | 1,0416... |
| 20 | 1 | THXY24 | 9.5.2020 | 3 |
| 60 | 5 | THXY24 | 9.5.2020 | 3 |
| 100 | 6 | THXY24 | 9.5.2020 | 3 |
| 33 | 1 | THXY25 | 11.6.2020 | ... |
I'm not sure if this is even possible in a single step but so far my ideas have only led to error messeges. I have the above data (with more rows which I'll leave out for clarity and more columns which are irrelevant to the calculation) and I am supposed to do the following:
There are always 2 or 3 rows per date and FrameID. I need to calculate a number with the values of the rows for every FrameID using a formula and then enter that number in the solution column at the end (red column). The formula for a FrameID is SOLUTION = ((100 * value(row w/ the SubIndex 5))/value(row w/ the SubIndex 1))/value(row w/ the SubIndex 6). In the case of the FrameID THXY24 and Date 7.2.2020 this give us ((100*55)/44)120 = 1,0416... . In the case of there only being rows with the SubIndexes 1 and 5 given for a FrameID (as is the case for FrameID THXY22) then we cannot calculate anything so the entry should be blank. There may be more than 3 rows per FrameID but they will always be distinguishable by the date. I need to calculate the solution for each FrameID (if possible, otherwise I need to add the blank) and enter it in the solution column but I am completely stuck (I'm a novice). Is there any way to do this?
Any help wouls be appreciated, thanks!
Here is one way to approach this in a calculated column. Please try this expression.
Result = VAR index1 = CALCULATE ( MIN ( Frame[Value] ), ALLEXCEPT ( Frame, Frame[FrameID], Frame[Date] ), Frame[SubIndex] = 1 ) VAR index5 = CALCULATE ( MIN ( Frame[Value] ), ALLEXCEPT ( Frame, Frame[FrameID], Frame[Date] ), Frame[SubIndex] = 5 ) VAR index6 = CALCULATE ( MIN ( Frame[Value] ), ALLEXCEPT ( Frame, Frame[FrameID], Frame[Date] ), Frame[SubIndex] = 6 ) RETURN IF ( NOT ( ISBLANK ( index6 ) ), 100 * DIVIDE ( index5, index1 ) / index6 )If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Hi,
This calculated column formula works
=DIVIDE(DIVIDE(100*CALCULATE(SUM(Data[Value]),FILTER(Data,Data[FrameID]=EARLIER(Data[FrameID])&&Data[Date]=EARLIER(Data[Date])&&Data[SubIndex]=5)),CALCULATE(SUM(Data[Value]),FILTER(Data,Data[FrameID]=EARLIER(Data[FrameID])&&Data[Date]=EARLIER(Data[Date])&&Data[SubIndex]=1))),CALCULATE(SUM(Data[Value]),FILTER(Data,Data[FrameID]=EARLIER(Data[FrameID])&&Data[Date]=EARLIER(Data[Date])&&Data[SubIndex]=6)))Hope this helps.
4 Replies
Here is one way to approach this in a calculated column. Please try this expression.
Result = VAR index1 = CALCULATE ( MIN ( Frame[Value] ), ALLEXCEPT ( Frame, Frame[FrameID], Frame[Date] ), Frame[SubIndex] = 1 ) VAR index5 = CALCULATE ( MIN ( Frame[Value] ), ALLEXCEPT ( Frame, Frame[FrameID], Frame[Date] ), Frame[SubIndex] = 5 ) VAR index6 = CALCULATE ( MIN ( Frame[Value] ), ALLEXCEPT ( Frame, Frame[FrameID], Frame[Date] ), Frame[SubIndex] = 6 ) RETURN IF ( NOT ( ISBLANK ( index6 ) ), 100 * DIVIDE ( index5, index1 ) / index6 )If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Hi,
This calculated column formula works
=DIVIDE(DIVIDE(100*CALCULATE(SUM(Data[Value]),FILTER(Data,Data[FrameID]=EARLIER(Data[FrameID])&&Data[Date]=EARLIER(Data[Date])&&Data[SubIndex]=5)),CALCULATE(SUM(Data[Value]),FILTER(Data,Data[FrameID]=EARLIER(Data[FrameID])&&Data[Date]=EARLIER(Data[Date])&&Data[SubIndex]=1))),CALCULATE(SUM(Data[Value]),FILTER(Data,Data[FrameID]=EARLIER(Data[FrameID])&&Data[Date]=EARLIER(Data[Date])&&Data[SubIndex]=6)))Hope this helps.
mahoneypat Ashish_Mathur Thank you both. Both of your solutions worked perfectly.
You are welcome. If my reply helped, please mark it as Answer.