Calculation between rows with conditions

[tod]

Hi,

I'm trying to create a custom column showing % from data between rows. Best way to show what I'm trying to do is the data set here:

Column A	Column B	Column C	Custom Column D
1	A	20	2%
1	B	1000	100%
1	C	75	8%
1	D	250	25%
2	A	30	2%
2	B	1500	100%
2	C	100	7%
2	D	50	3%
3	A	10	1%
3	B	800	100%
3	C	20	3%
3	D	20	3%

The % calculation is always against B, but B repeats in Column B because Column A has several sets like different years. DAX or M formula is fine, but the data set is very large so perfomance is a consideration. Appreciate any and all help!

[v-yalanwu-msft · Accepted solution]

Hi @tod  ,  

You could create a measure by the following formula: 

Custom Column D =
MAX ( [Column C] )
    / CALCULATE (
        MAX ( 'Table'[Column C] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Column A] = MAX ( 'Table'[Column A] ) ),
        'Table'[Column B] = "B"
    )

The final output is shown below:  

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.   

[tod]

Thanks Pat. Using your formula, Columns A and C are summing as shown. They should remain in their original format. None of the columns are "summarized"; they are "Don't summarize".

This is how I interpreted your formula:

Custom Custom =

VAR thisvalue = 'Table'[Column C]

VAR thisAvalue = 'Table'[Column A]

VAR thisAvalueB =

AVERAGEX (

FILTER ( 'Table', 'Table'[Column A] = thisAvalue && 'Table'[Column B] = "B" ),

'Table'[Column C]

)

RETURN

DIVIDE ( thisvalue, thisAvalueB )

Did I do this right?

[mahoneypat]

Please try this column expression.  Rename the Table from PctOfB to your actual table name throughout.

PctOfBColumn =
VAR thisvalue = PctOfB[Column C]
VAR thisAvalue = PctOfB[Column A]
VAR thisAvalueB =
    AVERAGEX (
        FILTER ( PctOfB, PctOfB[Column A] = thisAvalue && PctOfB[Column B] = "B" ),
        PctOfB[Column C]
    )
RETURN
    DIVIDE ( thisvalue, thisAvalueB )

Pat