Dynamic segmentation

[tlamoureux]

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 :

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

• Then a query (query2) with daily shop performance ranking

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

• And finally a query (query3) with the last rank (by date)

ShopID	LastRank
1	6,9
2	8,2

My report is obvously composed of several filters :

• ShopName
• ShopTown
• Year
• Year-Week
• Year-Month

And several graphs :

• Table with ShopName and LastRank
• Graph (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 segmentation

My issue is for the last element. First of all, the segmentation is quite simple : it is a floor of the average rank :

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

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 segment

To 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]

Not very clear, but refer

https://community.powerbi.com/t5/Desktop/BIN-Range-Dynamic-Segmentation-based-on-measure/m-p/879288

https://community.powerbi.com/t5/Desktop/Dynamic-Segmentation-with-duplicate-rows/m-p/379859

[tlamoureux]

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 :

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

If I apply no filter, I will have this result

Rank	Number Of Shop
0	0
1	0
2	0
3	0
4	0
5	0
6	1
7	0
8	1
9	0

Then, if I apply the filter Year=2020, the result will be :

Rank	Number Of Shop
0	0
1	0
2	0
3	0
4	0
5	0
6	0
7	0
8	0
9	1

First 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]

Hi,

I'm still stuck with this problem. Can someone help ?

Thank you very much,

Thierry.

[tlamoureux]

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] >= Buckets[Lower]
                    && [Total sales] <= Buckets[Upper]
            )
        ) > 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.

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 ?

Thank you very much,

Thierry