cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

Need Calculated Column to compute dynamically Range based on Slicers selection

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.

 

RangeMinRangeMaxRangeName
099< 100
100499100- < 500
500999500- < 1000
100019991000 - < 2000
200050000> 2000

 

 

Customers Table:

 

CustomersDateCategoryDivisionQty
ABC1/1/2019AWifi1200
ABC2/1/2019AWifi2300
ABC5/1/2019BWifi570
ABC7/1/2019BWifi980
DEF3/1/2019AWifi750
DEF2/2/2019AWifi340
DEF3/1/2019BWifi490
DEF2/2/2019BWifi560

 

For Example:

1) When all values in slicers are selected

RangeNameQty
> 20007190

 

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

CustomersDateCategoryDivisionQty
ABC5/1/2019BWifi570
ABC7/1/2019BWifi980

 

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

RangeNameQty
1000 - < 20001550

 

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.

 

2 ACCEPTED SOLUTIONS
Resident Rockstar
Resident Rockstar

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.

View solution in original post

Super User IV
Super User IV

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

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

9 REPLIES 9
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Super User IV
Super User IV

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

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

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 !!

Resident Rockstar
Resident Rockstar

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.

View solution in original post

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

Resident Rockstar
Resident Rockstar

@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 )
)



@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

 

Resident Rockstar
Resident Rockstar

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 

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 !!

 

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

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

Top Solution Authors
Top Kudoed Authors