Dynamic range in calculated column

[dkotlarov]

Hi every one.

I really enjoy this forum an i serch it frequently for solutions to problems i encounter. This time i am realy struggling with something.

I have calculated table in which i am consulidating the salse for customers by date and some others parameters. 

Summary (day) =

    SUMMARIZE('Delivery','Delivery'[Region-Warehouse],'Delivery'[Delivery date], 'Delivery'[Customer id], 'Delivery'[Customer name],'Delivery'[KEY],

                "Orders",                  DISTINCTCOUNT('Delivery'[Sales order id]),

                "Order value",             SUM('Delivery'[Order value])

             ))

In that table i am calculating a column to determin the ranges of Order value:

Sum ranges AVG =

    VAR suma = 'Summary (day)'[Order value]

return

    IF(and(suma>0,     suma<=20),         "(0 - 20]",

    IF(and(suma>20,    suma<=30),         "(20 - 30]",

    IF(and(suma>30,     suma<=40),        "(30 - 40]",

    IF(and(suma>40,     suma<=50),        "(40 - 50]",

    IF(and(suma>50,     suma<=60),        "(50 - 60]",

    IF(and(suma>60,     suma<=70),        "(60 - 70]",

    IF(and(suma>70,     suma<=80),        "(70 - 80]",

    IF(and(suma>80,     suma<=90),        "(80 - 90]",

    IF(and(suma>90,     suma<=100),       "(90 - 100]",

    IF(and(suma>100,     suma<=110),      "(100 - 110]",

    IF(and(suma>110,     suma<=120),      "(110 - 120]",

    IF(and(suma>120,     suma<=130),      "(120 - 130]",

    IF(and(suma>130,     suma<=140),      "(130 - 140]",

    IF(and(suma>140,     suma<=150),      "(140 - 150]",

    iF(and(suma>150,    suma<=200),       "(150 - 200]",

    iF(and(suma>200,    suma<=300),       "(200 - 300]",

    iF(and(suma>300,    suma<=500),       "(300 - 500]",

    iF(suma>500,                                       "( >500)",

                                          "Promotional materials"

    ))))))))))))))))))

What i want to change this ranges dynamically by using sliser to sect the range. The sliser will have differant ranges. For example:

(0 - 20], (0 - 30], (0 - 40], (0 - 50], (0 - 60], (0 - 70], (0 - 80], (0 - 90], (0 - 100], (0 - 110] .............

The problem is, the rows whith sumes in range from 0 to 100 must be includet in range (0 - 20] if that range is selected in the sliser. If the selection in the sliser is (0 - 50], that must incude all orders with ranges (0 - 20], (0 - 30], (0 - 40] and finally (0 - 50].

Thank you all. You are saving me frequently without even knowing it.  

[dkotlarov · Accepted solution]

Hi everyone. I actually find a way to deal with the problem. The solution is to creat separate table with needed ranges and use "SELECTEDVALUE" as a part of the filter in "CALCULATE" function. For example:

Order value =

    CALCULATE(

        SUM(Summary (day)'[Order value]),

        'Summary (day)'[Order value]<=SELECTEDVALUE('Active slicing'[Order value]

    ))

As a result i can use slicer to actively change the sum ranges associated wiht the value in calculated table.