Calculated column using data from multiple rows and columns

[Anonymous]

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!

[mahoneypat · Accepted solution]

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

[Anonymous]

@mahoneypat @Ashish_Mathur Thank you both. Both of your solutions worked perfectly. 

[Ashish_Mathur]

You are welcome.  If my reply helped, please mark it as Answer.

[Ashish_Mathur · Accepted solution]

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 · Accepted solution]

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