Forum Discussion
Diagonal Calculation with multiple columns and dates involved
Hi, I am trying to solve an interesting and challenging problem.
| mth | 1.C | 2.A | 3.B | 4.D | 5.E |
| 2021-09 | 1100 | 4800 | |||
| 2021-10 | 2000 | 3000 | 4000 | ||
| 2021-11 | 3000 | 4000 | 6000 | 7000 | |
| 2021-12 | 4000 | 5000 | 7000 | 9000 | 1000 |
I want to calculate diagonally and show the ouput as below
Note : I have also given new column names
| mth | 0-1 | 1-2 | 2-3 | 3-4 |
| 2021-10 | 273% | 83.3% | ||
| 2021-11 | 200.0% | 50.0% | 57.1% | |
| 2021-12 | 60.0% | 57.1% | 66.7% | 700.0% |
Formula : (row 2, column 2) 3000 / (row1,column1) 1100 = 273%
HI akshbhala,
You can do unpivot columns on your fields to convert them to attribute and value, then remove the character to only keep number in your attribute fields.
Unpivot columns - Power Query | Microsoft Learn
After these steps, you can create segment table with different ranges and use with raw table field and new table segment to create matrix. Then you can write a measure to look up and calculate value based on current category and segment.
Dynamic segmentation – DAX Patterns
Regards,Xiaoxin Sheng
2 Replies
Hi,
row number or column number are relative things, you should have a exact definition for thes rows, columns
example: row 2 is current month and row 1 is prevous month, and column 2 is current week, and column 1 is previous week. That possible make DAX function
HI akshbhala,
You can do unpivot columns on your fields to convert them to attribute and value, then remove the character to only keep number in your attribute fields.
Unpivot columns - Power Query | Microsoft Learn
After these steps, you can create segment table with different ranges and use with raw table field and new table segment to create matrix. Then you can write a measure to look up and calculate value based on current category and segment.
Dynamic segmentation – DAX Patterns
Regards,Xiaoxin Sheng
