Forum Discussion
Calculation between rows with conditions
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!
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.
3 Replies
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.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
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])RETURNDIVIDE ( thisvalue, thisAvalueB )Did I do this right?
