Dynamic range in calculated column

Question

Hi every one.&nbsp;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.&nbsp;&nbsp;Summary (day) =&nbsp; &nbsp; SUMMARIZE('Delivery','Delivery'[Region-Warehouse],'Delivery'[Delivery date], 'Delivery'[Customer id], 'Delivery'[Customer name],'Delivery'[KEY],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "Orders", &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;DISTINCTCOUNT('Delivery'[Sales order id]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "Order value", &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUM('Delivery'[Order value])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;))&nbsp;In that table i am calculating a column to determin the ranges of Order value:&nbsp;Sum ranges AVG = &nbsp; &nbsp; VAR suma = 'Summary (day)'[Order value]return&nbsp; &nbsp; IF(and(suma&gt;0, &nbsp; &nbsp; suma&lt;=20), &nbsp; &nbsp; &nbsp; &nbsp; "(0 - 20]",&nbsp; &nbsp; IF(and(suma&gt;20, &nbsp; &nbsp;suma&lt;=30), &nbsp; &nbsp; &nbsp; &nbsp; "(20 - 30]",&nbsp; &nbsp; IF(and(suma&gt;30, &nbsp; &nbsp; suma&lt;=40), &nbsp; &nbsp; &nbsp; &nbsp;"(30 - 40]",&nbsp; &nbsp; IF(and(suma&gt;40, &nbsp; &nbsp; suma&lt;=50), &nbsp; &nbsp; &nbsp; &nbsp;"(40 - 50]",&nbsp; &nbsp; IF(and(suma&gt;50, &nbsp; &nbsp; suma&lt;=60), &nbsp; &nbsp; &nbsp; &nbsp;"(50 - 60]",&nbsp; &nbsp; IF(and(suma&gt;60, &nbsp; &nbsp; suma&lt;=70), &nbsp; &nbsp; &nbsp; &nbsp;"(60 - 70]",&nbsp; &nbsp; IF(and(suma&gt;70, &nbsp; &nbsp; suma&lt;=80), &nbsp; &nbsp; &nbsp; &nbsp;"(70 - 80]",&nbsp; &nbsp; IF(and(suma&gt;80, &nbsp; &nbsp; suma&lt;=90), &nbsp; &nbsp; &nbsp; &nbsp;"(80 - 90]",&nbsp; &nbsp; IF(and(suma&gt;90, &nbsp; &nbsp; suma&lt;=100), &nbsp; &nbsp; &nbsp; "(90 - 100]",&nbsp; &nbsp; IF(and(suma&gt;100, &nbsp; &nbsp; suma&lt;=110), &nbsp; &nbsp; &nbsp;"(100 - 110]",&nbsp; &nbsp; IF(and(suma&gt;110, &nbsp; &nbsp; suma&lt;=120), &nbsp; &nbsp; &nbsp;"(110 - 120]",&nbsp; &nbsp; IF(and(suma&gt;120, &nbsp; &nbsp; suma&lt;=130), &nbsp; &nbsp; &nbsp;"(120 - 130]",&nbsp; &nbsp; IF(and(suma&gt;130, &nbsp; &nbsp; suma&lt;=140), &nbsp; &nbsp; &nbsp;"(130 - 140]",&nbsp; &nbsp; IF(and(suma&gt;140, &nbsp; &nbsp; suma&lt;=150), &nbsp; &nbsp; &nbsp;"(140 - 150]",&nbsp; &nbsp; iF(and(suma&gt;150, &nbsp; &nbsp;suma&lt;=200), &nbsp; &nbsp; &nbsp; "(150 - 200]",&nbsp; &nbsp; iF(and(suma&gt;200, &nbsp; &nbsp;suma&lt;=300), &nbsp; &nbsp; &nbsp; "(200 - 300]",&nbsp; &nbsp; iF(and(suma&gt;300, &nbsp; &nbsp;suma&lt;=500), &nbsp; &nbsp; &nbsp; "(300 - 500]",&nbsp; &nbsp; iF(suma&gt;500,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"( &gt;500)",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "Promotional materials"&nbsp; &nbsp; ))))))))))))))))))&nbsp;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],&nbsp;(0 - 30],&nbsp;(0 - 40],&nbsp;(0 - 50],&nbsp;(0 - 60],&nbsp;(0 - 70],&nbsp;(0 - 80],&nbsp;(0 - 90],&nbsp;(0 - 100],&nbsp;(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&nbsp;(0 - 50].&nbsp;Thank you all. You are saving me frequently without even knowing it.&nbsp;&nbsp;

dkotlarov · Accepted Answer

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.

Forum Discussion

Dynamic range in calculated column

1 Reply

Related content

Dynamic Variable in Calculated Columns

Dynamic Rank Calculation

TMDL - calculated column

Dynamic Calculation Different Period

Dynamic Calculated Column as Visual dimension