Forum Discussion

Mond's avatar
Mond
Helper III
6 years ago
Solved

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.

 

Need Help
Show and Tell
Tips and Tricks

9 Replies

  • sturlaws's avatar
    sturlaws
    Resident Rockstar
    6 years ago

    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's avatar
      Mond
      Helper III
      6 years ago

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

       

    • Mond's avatar
      Mond
      Helper III
      6 years ago

      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's avatar
        sturlaws
        Resident Rockstar
        6 years ago

        Mond 

         

        try these two measures(based on Anonymouss 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 )
)



  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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-with-Winners/ba-p/890814
    https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
    https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
    https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

     

     

    • Mond's avatar
      Mond
      Helper III
      6 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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