Calculated column from table with multiple ranges

[KasperJ90]

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!

[OzkanDhont · Accepted solution]

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