Dynamic grouping Power BI

[Anonymous]

Hi

I need to create a Clustered bar chart, that displays the total number of complaints received grouped by the difference between the production date of an item and the date in which I received the complain, so for example I have Item A and I need the number of complaints received based on the following groups 0-30 days, 31- 60 days, 61- 90 days and > 90 days, where the days represent the difference between the two dates mentioned before.

However I need this groups to be dynamic according to the item I select on a slicer, so for example Item A has the groups mentioned before, while Item B has groups as 0-15 days, 16-25 days, > 26 days, Item C 0 - 20 days, 21-30 days, 31-90 days > 90 days, and so on; therefore I also need that the final user is able to select the different ranges of days to create the different groups. Is there any way I can achieve this result.

Thank you so much

[v-xiaotang · Accepted solution]

Hi @Anonymous 

Thanks for reaching out to us.

For the visual axis, it only supports columns, but columns cannot be generated dynamically by slicer. So there are two workarounds.

(1) Create an auxiliary visual

(2) use tooltip

Here are all the measures used (just take product A as an example):

A Range = 
var _v1= [Slicer1 Value]
var _v2=[Slicer2 Value]
var _v3=[Slicer3 Value]
var _cur= MIN(Stage[Column1])
return 
SWITCH(TRUE(),
_cur="Stage 1","0-"&_v1&" days",
_cur="Stage 2",_v1&"-"&_v2&" days",
_cur="Stage 3",_v2&"-"&_v3&" days",
_cur="Stage 4",">"&_v3&" days")

A DaysPerComplain = 
var _Pd= CALCULATE(MAX('production date list'[production date]),'production date list'[Product]= MIN('Table'[Product]))
return DATEDIFF(_Pd,MIN('Table'[complain date]),DAY)

A Count = 
var _cur= MIN(Stage[Column1])
var _PName= "Product A"
return 
SWITCH(TRUE(),
_cur="Stage 1",CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[Product]=_PName&& [A DaysPerComplain]<= [Slicer1 Value])),
_cur="Stage 2",CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[Product]=_PName&& [A DaysPerComplain]> [Slicer1 Value] && [A DaysPerComplain]<= [Slicer2 Value])),
_cur="Stage 3",CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[Product]=_PName&& [A DaysPerComplain]> [Slicer2 Value] && [A DaysPerComplain]<= [Slicer3 Value])),
_cur="Stage 4",CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[Product]=_PName&& [A DaysPerComplain]> [Slicer3 Value])))

For more please see my sample file attached below.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi v-xiotang, thank you so much for your reply, I solved this before using also the parameters as you but your solution helped me to improve what I had done before. Thanks a lot for your answer I really appreciate it.

[amitchandak]

@Anonymous , You to create a measure that displays these values. say at the customer, complaints level .

Have a table with all these values and join it back in measure using customer complaints  in summarize in a measure

Please check the code below, you need equal to and you need to summarize as you two columns not one.

if only complaints level can suffice you can use the values.

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1387187#M626

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

[Anonymous]

Hi Amit

Thank you for your answer, however I would like to know if it's possibile to make the measure that you named "Margin Type" dynamic, in the sense that I need the limits of each group to change according to what the user selects in a slicer for the limits, this groups also change according to the product I filter. I attach an imagen to show you how it must work

Then this will be visualized in a clustered br chart

Thanks a lot