Forum Discussion
Need Calculated Column to compute dynamically Range based on Slicers selection
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.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
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.
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 !!
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
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 ) )
