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