cancel
Showing results for
Did you mean:
Frequent Visitor

Dynamic range in calculated column

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.
1 ACCEPTED SOLUTION
Frequent Visitor

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.

Frequent Visitor

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.

Announcements

Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

Power BI March 2023 Update

Find out more about the March 2023 update.

March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors