Forum Discussion
Dynamic grouping Power BI
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
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.
4 Replies
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-kHi 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
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.
