Dynamic segmentation

Question

Hello,I am trying to give a chance to PowerBI and so I'm migrating the most representative of my Qlik Sense report.I am facing an issue with something I call here dynamic segmentation.My report aims to display rank statistics on shop :There is a query (query1) with shops name et properties :ShopIDShopNameShopTown1Shop1Toulouse2Shop2Toulouse3Shop3NiceThen a query (query2) with daily shop performance rankingShopIDDateDailyRank112/01/20205,6113/01/20206,2114/02/20207212/01/20208,5...&nbsp;&nbsp;And finally a query (query3) with the last rank (by date)ShopIDLastRank16,928,2&nbsp;My report is obvously composed of several filters :ShopNameShopTownYearYear-WeekYear-MonthAnd several graphs :Table with ShopName and LastRankGraph (curve) with average rank evolution by Year-Month (2019-01, 2019-02, ...) or Year-Week (2019-40, 2019-41, ...)A bar graph with the total of shop by rank segmentationMy issue is for the last element. First of all, the segmentation is quite simple : it is a floor of the average rank :SegmentMinRankMaxRank000,99111,99222,99333,99444,99555,99666,99777,99888,99999,99&nbsp;In Qlik Sense, it is very simple, I have created a dimension with the formula : RankSegment=FLOOR(AGGR(AVG(Query2[Rank]), Query2[Shop])) :AGGR(AVG(Query2[Rank]), Query2[Shop]) : Create an aggregation on Rank average, by Shop taking to account the active filter (this seems difficult with PowerBI)FLOOR : Give the segmentTo end, I had just to create a bar graph using RankSegment as dimension and COUNT(Query2[Shop]) as measure.I think the trick must be done in the report and not in the request window, otherwise the filters applied in the report will not be take to account. Indeed, the number of shops in the rank 9 in 2019 is not the same that the number of shops in 2020.I hope I've been clear enough.Thank you very much for your help.Regards,Thierry

amitchandak · Answer

Not very clear, but referhttps://community.powerbi.com/t5/Desktop/BIN-Range-Dynamic-Segmentation-based-on-measure/m-p/879288https://community.powerbi.com/t5/Desktop/Dynamic-Segmentation-with-duplicate-rows/m-p/379859

tlamoureux · Answer

Hello,Thank you for your answer but I have already found this thread and it does not help.Here is a simple illustration of what I need.If query2 is composed of these elements :ShodIDShopNameDateRank1Toulouse01/01/201981Toulouse02/01/201981Toulouse03/01/201981Toulouse01/01/202091Toulouse02/01/202092Nice01/01/20196&nbsp;If I apply no filter, I will have this resultRankNumber Of Shop00102030405061708190Then, if I apply the filter Year=2020, the result will be :RankNumber Of Shop00102030405060708091First I calculate the aggregation on the average in the time periode and then I apply the floor function to obtain the rank.Thanks for help.Thierry

tlamoureux · Answer

Hi,I'm still stuck with this problem. Can someone help ?Thank you very much,Thierry.

tlamoureux · Answer

Hello,I've found by myself but I still have some question. I used this thread with the bellow DAX formula :Segmented stores =
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            VALUES ( Data[StoreID] ),
            "ABCD",
            CALCULATE (
                [Total sales],
                CALCULATETABLE ( VALUES ( Data[StoreID] ) ),
                ALLSELECTED ()
            )
        ),
        COUNTROWS (
            FILTER (
                Buckets,
                [Total sales] &gt;= Buckets[Lower]
                    &amp;&amp; [Total sales] &lt;= Buckets[Upper]
            )
        ) &gt; 0
    )
)I've two questions, the first is about the CALCULATE, I don't understand why the VALUES(Data[StoreID]) is in the CALCULATETABLE function. If I only put the VALUES function it does not work but I do not see the difference as there is no argument for CALCULATETABLE. Then, I do not understand the goal of the ALLSELECTED(), if delete this line, I have the same result.&nbsp;I have inserted in my report this bar graph :The value are exactly what I expected but, when I click on a bar, the other element of my report are not updated : if I click on the bar "0 à 1", the list of shops may be reduced to a number of 5. Do you have an idea why it does not work ?&nbsp;Thank you very much,Thierry

Forum Discussion

Dynamic segmentation

4 Replies

Related content

Dynamic Segmentation

|| Dynamic Segmentation : The formula ||

Dynamic Customer Segmentation

filter with segmentation

Segment

ShopID	ShopName	ShopTown
1	Shop1	Toulouse
2	Shop2	Toulouse
3	Shop3	Nice

ShopID	Date	DailyRank
1	12/01/2020	5,6
1	13/01/2020	6,2
1	14/02/2020	7
2	12/01/2020	8,5
...

Segment	MinRank	MaxRank
0	0	0,99
1	1	1,99
2	2	2,99
3	3	3,99
4	4	4,99
5	5	5,99
6	6	6,99
7	7	7,99
8	8	8,99
9	9	9,99

ShodID	ShopName	Date	Rank
1	Toulouse	01/01/2019	8
1	Toulouse	02/01/2019	8
1	Toulouse	03/01/2019	8
1	Toulouse	01/01/2020	9
1	Toulouse	02/01/2020	9
2	Nice	01/01/2019	6

ShopID	LastRank
1	6,9
2	8,2