cancel
Showing results for
Search instead for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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.

1 REPLY 1
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.

## Helpful resources

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors