Need Calculated Column to compute dynamically Range based on Slicers selection

[Mond]

Hi,

I have a requirement for RangeName and Qty to be displayed with selection of multiple slicers.

I need a calculated column since, this RangeName has to be displayed as a Legend in the pie chart.

RangeTable is assumption only to get the legend values.

The challenge is when values of slicers (Division, Customers, Category, Date) are selected, then the RangeName should dynamically calculate and display the value of sum(Qty) with what the filtered criteria.

RangeMin	RangeMax	RangeName
0	99	< 100
100	499	100- < 500
500	999	500- < 1000
1000	1999	1000 - < 2000
2000	50000	> 2000

Customers Table:

Customers	Date	Category	Division	Qty
ABC	1/1/2019	A	Wifi	1200
ABC	2/1/2019	A	Wifi	2300
ABC	5/1/2019	B	Wifi	570
ABC	7/1/2019	B	Wifi	980
DEF	3/1/2019	A	Wifi	750
DEF	2/2/2019	A	Wifi	340
DEF	3/1/2019	B	Wifi	490
DEF	2/2/2019	B	Wifi	560

For Example:

1) When all values in slicers are selected

RangeName	Qty
> 2000	7190

2) When few values in slicers are selected, below raw data

Customers	Date	Category	Division	Qty
ABC	5/1/2019	B	Wifi	570
ABC	7/1/2019	B	Wifi	980

Based on above data, it shoud dynamically get the rangename with qty

RangeName	Qty
1000 - < 2000	1550

May be I am calculating wrong, but, challenge is, when slicers are selected, the RangeName is giving Range based on entire underlying data set from the table not honoring the selection criteria of slicers.

I can get proper range on Measure but not on a calculated column.

Thanks for your inputs.

[sturlaws · Accepted solution]

Hi, @Mond,

you can achieve this by creating a measure which has as sole purpose to filter the range-table:

RangeFilterMeasure = 
var _sumQty=SUM(customer[Qty])
return
SWITCH(
    TRUE();
    _sumQty >= 2000 && SELECTEDVALUE(Range[Range])=">=2000";1;    
    _sumQty >= 1000 && _sumQty < 2000 && SELECTEDVALUE(Range[Range])="1000 - <2000";1;    
    _sumQty >= 500 && _sumQty < 1000 && SELECTEDVALUE(Range[Range])="500 - <1000";1;
    _sumQty >= 100 && _sumQty < 500 && SELECTEDVALUE(Range[Range])="100 - <500";1;
    _sumQty >= 0 && _sumQty < 100 && SELECTEDVALUE(Range[Range])="<100";1;
    0
)

Add this RangeFilterMeasure to the filter pane of your visual and set it to filter on RangeFilterMeasure=1. Add 'Range'[Range] to the legend field of your pie chart, and e.g. date to the details-field. I have created an example for you.

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[v-shex-msft]

Hi @Mond ,

You can use following measure formula to achieve your requirement:

Measure = 
VAR rMin =
    MIN ( Range[RangeMin] )
VAR rMax =
    MAX ( Range[RangeMax] )
RETURN
    CALCULATE (
        SUM ( Sales[Qty] ),
        FILTER ( ALLSELECTED ( Sales ), [Qty] >= rMin && [Qty] <= rMax ),
        VALUES ( Sales[Customers] )
    )

Regards,

Xiaoxin Sheng

[amitchandak · Accepted solution]

The above suggestion seems fine. But if you want to fix the range at customer level, You have to do small change.

refer :https://community.powerbi.com/t5/Desktop/SUM-of-AVERAGE/td-p/197013

RangeFilterMeasure = 
var _sum= SUM (customer[Qty] ),
        ALLEXCEPT ( 'Customer','Customer'[ID])
return
SWITCH(
    TRUE();
    _sum >= 2000 && SELECTEDVALUE(Range[Range])=">=2000";1;    
    _sum >= 1000 && _sumQty < 2000 && SELECTEDVALUE(Range[Range])="1000 - <2000";1;    
    _sum >= 500 && _sumQty < 1000 && SELECTEDVALUE(Range[Range])="500 - <1000";1;
    _sum >= 100 && _sumQty < 500 && SELECTEDVALUE(Range[Range])="100 - <500";1;
    _sum >= 0 && _sumQty < 100 && SELECTEDVALUE(Range[Range])="<100";1;
    0
)

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Mond]

Thank you so much for further ideas.

I need to find out DISTINCTCOUNT on customers with same Qty Range.

How many fall under Qty Range ?

Thanks once again for support !!

[sturlaws · Accepted solution]

Hi, @Mond,

you can achieve this by creating a measure which has as sole purpose to filter the range-table:

RangeFilterMeasure = 
var _sumQty=SUM(customer[Qty])
return
SWITCH(
    TRUE();
    _sumQty >= 2000 && SELECTEDVALUE(Range[Range])=">=2000";1;    
    _sumQty >= 1000 && _sumQty < 2000 && SELECTEDVALUE(Range[Range])="1000 - <2000";1;    
    _sumQty >= 500 && _sumQty < 1000 && SELECTEDVALUE(Range[Range])="500 - <1000";1;
    _sumQty >= 100 && _sumQty < 500 && SELECTEDVALUE(Range[Range])="100 - <500";1;
    _sumQty >= 0 && _sumQty < 100 && SELECTEDVALUE(Range[Range])="<100";1;
    0
)

Add this RangeFilterMeasure to the filter pane of your visual and set it to filter on RangeFilterMeasure=1. Add 'Range'[Range] to the legend field of your pie chart, and e.g. date to the details-field. I have created an example for you.

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[Mond]

Hi Sturla,

After getting this formula, I need to show values in a pie chart where the legend remains "Range" and value is "Qty".

When we select slicers, the pie chart should show,

1) how much Qty is in each of "Range"

     For Example: 

     500- <1000 total qty is (570+980+900) = 2450

     1000- <2000 total qty is (1200+1240) = 2440

    >=2000 total qty is (2300) = 2300

As slicers are selected, the Pie Chart also changes with Range & Qty (summed up).

Thanks

[sturlaws]

@Mond 

try these two measures(based on @v-shex-msfts post):

Measure count = 
VAR rMin =
    MIN ( Range[RangeMin] )
VAR rMax =
    MAX ( Range[RangeMax] )
RETURN
    CALCULATE (
        count ( customer[Customers] );
        FILTER (  customer ; [Qty] >= rMin && [Qty] <= rMax )
    )

Measure sum = 
VAR rMin =
    MIN ( Range[RangeMin] )
VAR rMax =
    MAX ( Range[RangeMax] )
RETURN
    CALCULATE (
        sum ( customer[qty] );
        FILTER ( customer ; [Qty] >= rMin && [Qty] <= rMax )
)

[Mond]

@sturlaws, Thank you but these measures result in same as yours.

But doesn't satisfy the 2nd request that I made.

I need to show how much total qty with in each of range in the pie chart.

Thank you

[sturlaws]

with the two last measures you can remove date from the details of the pie chart, and it will sum the values of each interval:
updated pbix 

[Mond]

Thank you very much !!

That worked like a charm 🙂.

On same data, I need to project, how many unique customers for the same range (Qty value) with different slicer selection.

I tried to replicate below formula with DISTINCTCOUNT, but it doesn't have range of count, it has to honor the Qty Range.

Thanks once again for your valuable support !!