Forum Discussion
Calculated column from table with multiple ranges
Hi,
Need your help to add a calculated column based on each category. I have table 1 showing my categories and ranges and then table 2 with the "operation number" to use to sum all minutes within the category range.
Table 1:
Category 1: 8000..8099|8200..8999
Category 2: 2000..7999|9000..9999
Table 2:
| Operation No. | Minutes |
| 8500 | 5 |
| 8999 | 10 |
| 2500 | 80 |
| 9999 | 100 |
Calculated column for category 1 = sum all minutes within category 1 ranges (in this case 15)
Calculated column for category 2 = sum all minutes within category 2 ranges (in this case 180)
Thank you in advance!
Hi KasperJ90 !
Do you need it to be a calculated column?
I've created something similar in the past but as a measure which will save up on your data model.
Measure =
CALCULATE(
SUM('Table (2)'[No.Minutes]),
FILTER(
'Table (2)',
COUNTROWS(
FILTER(
'Table (1)',
'Table (2)'[Operations] > 'Table (1)'[Min] &&
'Table (2)'[Operations] <= 'Table (1)'[Max]
)
)
)
)You can download the pbix file for better understanding:
https://filetransfer.io/data-package/ltw0G1YS#link
Hope it helped!
Kind regards,
OD
1 Reply
Hi KasperJ90 !
Do you need it to be a calculated column?
I've created something similar in the past but as a measure which will save up on your data model.
Measure =
CALCULATE(
SUM('Table (2)'[No.Minutes]),
FILTER(
'Table (2)',
COUNTROWS(
FILTER(
'Table (1)',
'Table (2)'[Operations] > 'Table (1)'[Min] &&
'Table (2)'[Operations] <= 'Table (1)'[Max]
)
)
)
)You can download the pbix file for better understanding:
https://filetransfer.io/data-package/ltw0G1YS#link
Hope it helped!
Kind regards,
OD