Summarize Table not dynamic on slicers - clustering as a legend

[Standef]

Hello everyone,

I have a MAIN_TABLE with Country, Store_ID, Product_ID, Month_Date, Sales, Quantities, Traffic and more…

Data is monthly as we are trying to show the Evolution of Item Prices by month; and another chart where I show the Evolution of Traffic, Average_Check, Sales by month.
The goal is to prove that movement in prices are affecting traffic and sales.

I have a slicer based on a parameter that allows me to change the Legend of the line charts. By doing so, I can have either 1 line per store, or 1 line per city, or 1 line per Region, and so on. This allows me to understand which region/city is raising prices more than the other, and see for each how their sales are being impacted.

Now here is the tricky part : in this "Store_Grouping" slicer, I want to be able to select an option that we will call "Price Evolution Grouping" that will split my stores by 4 groups with the same number of stores : top 25% of stores that rose their prices the most, middle top 25%, middle down 25% and Bottom 25% that increased their prices the most.

We managed to have this working with a SUMMARIZE table that looks like this :

 

Table Store Level =

SUMMARIZE(

    'MAIN_TABLE',

        'MAIN_TABLE'[COUNTRY],

        'MAIN_TABLE'[STORE_ID],

        "Price Variation",

            [priceevolution]

        )

To which we’ve added a column that counts the number of stores for each country, another that ranks these stores based on the priceevolution measure, and a third column that uses these two first to assing a percentile group.
Here are the columns' code:

Count of stores = COUNTX(FILTER(ALL('Table Store Level'),'Table Store Level'[COUNTRY] = EARLIER('Table Store Level'[COUNTRY])),'Table Store Level'[COUNTRY])

Store rank = RANKX(FILTER(ALL('Table Store Level'),'Table Store Level'[COUNTRY] = EARLIER('Table Store Level'[COUNTRY])),'Table Store Level'[Price Variation],,DESC)

Percentile Group =

IF(

    DIVIDE('Table Store Level'[Store rank],'Table Store Level'[Count of stores]) <= 0.25,

    "25% Higher Increase",

    IF(

        DIVIDE('Table Store Level'[Store rank],'Table Store Level'[Count of stores]) <= 0.5,

        "25% middle higher increase",

        IF(

            DIVIDE('Table Store Level'[Store rank],'Table Store Level'[Count of stores]) <= 0.75,

            "25% middle lower increase",

            "25% lower increase")))

But all this only works at the country level, meaning that we get the stores grouped (or clustered) in 4 equal groups based on the blended price increase of all products, but if I filter with a slicer on the item 123, the clusters are not changing as they should.

It seems like the SUMMARIZE method is not dynamic on slicers, and we have been stuck on this for quite a while - is there any way to get this summarize dynamic ? Or any other way to achieve what we want to do ? Any help would be much appreciated!

Thanks a lot!

Stan




FYI, The [priceevolution] measure looks like this :

Priceevolution =

Var firstsales = CALCULATE (

            sum ( 'MAIN_TABLE'[SALES] ),

            FILTER (

                ALL ( 'MAIN_TABLE'[Month_Date] ),

                'MAIN_TABLE'[Month_Date] = MIN ( 'MAIN_TABLE'[Month_Date] )

            ))

Var lastsales = CALCULATE (

            sum ( 'MAIN_TABLE'[sales] ),

            FILTER (

                ALL ( 'MAIN_TABLE'[Month_Date] ),

                'MAIN_TABLE'[Month_Date] = MAX ( 'MAIN_TABLE'[Month_Date] )

            ))

Var firstquantities = CALCULATE (

            sum ( 'MAIN_TABLE'[QUANTITY] ),

            FILTER (

                ALL ( 'MAIN_TABLE'[Month_Date] ),

                'MAIN_TABLE'[Month_Date] = MIN ( 'MAIN_TABLE'[Month_Date] )

            ))

Var lastquantities = CALCULATE (

            sum ( 'MAIN_TABLE'[QUANTITY] ),

            FILTER (

                ALL ( 'MAIN_TABLE'[Month_Date] ),

                'MAIN_TABLE'[Month_Date] = MAX ( 'MAIN_TABLE'[Month_Date] )

            ))

 

Var firstprice = firstsales / firstquantities
Var lastprice = lastsales / lastquantities

return

(lastprice - firstprice) / lastprice

[123abc]

To make the clustering dynamic based on slicer selections, you can try using DAX measures instead of calculated columns. Measures are dynamic and respond to slicer changes. Here's a modified version of your DAX code using measures:

Count of stores =
CALCULATE (
COUNTROWS('MAIN_TABLE'),
ALLEXCEPT('MAIN_TABLE', 'MAIN_TABLE'[COUNTRY])
)

Store rank =
RANKX(
FILTER(
ALLSELECTED('MAIN_TABLE'),
'MAIN_TABLE'[COUNTRY] = VALUES('MAIN_TABLE'[COUNTRY])
),
[Price Variation],
,
DESC
)

Percentile Group =
IF(
DIVIDE([Store rank], [Count of stores]) <= 0.25,
"25% Higher Increase",
IF(
DIVIDE([Store rank], [Count of stores]) <= 0.5,
"25% middle higher increase",
IF(
DIVIDE([Store rank], [Count of stores]) <= 0.75,
"25% middle lower increase",
"25% lower increase"
)
)
)

Ensure that the 'Priceevolution' measure remains the same.

Now, use these measures in your visualizations instead of the calculated columns. When you use the slicer, the measures will respond dynamically to the selected items, and the clustering should adjust accordingly.

Make sure to update your visuals to use these measures, and let me know if this resolves your issue.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Standef]

Thanks for the reply @123abc ! 
Unfortunately PowerBI doesn't allow to put a measure as a legend in a line chart; the legend must be a column or calculated column.. 

[123abc]

Thank you for providing additional context. If you need to use the percentile grouping as a legend in a line chart, you may want to create a supporting table with the necessary columns for your legend.

Here's how you can modify your DAX code to create a table that includes the necessary information for the legend:

PriceEvolutionTable =
SUMMARIZE(
'MAIN_TABLE',
'MAIN_TABLE'[COUNTRY],
'MAIN_TABLE'[STORE_ID],
'MAIN_TABLE'[Product_ID],
"Price Variation",
[priceevolution],
"Count of stores", CALCULATE(COUNTROWS('MAIN_TABLE')),
"Store rank", RANKX(ALL('MAIN_TABLE'), 'MAIN_TABLE'[priceevolution], , DESC),
"Percentile Group",
IF(
[Store rank] / [Count of stores] <= 0.25,
"25% Higher Increase",
IF(
[Store rank] / [Count of stores] <= 0.5,
"25% middle higher increase",
IF(
[Store rank] / [Count of stores] <= 0.75,
"25% middle lower increase",
"25% lower increase"
)
)
)
)

This will create a table ('PriceEvolutionTable') with the necessary columns for your legend. You can then use this table in your line chart, selecting the "Percentile Group" column as the legend.

This approach allows you to have a separate table that provides the legend values based on the dynamic percentile grouping. Make sure to set up relationships appropriately in your data model to connect this new table with your existing data.

With this setup, when you filter using a slicer on a specific item, the line chart should dynamically adjust based on the selected item while still using the desired legend.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Standef]

for the RANKX, this gives me this error: "there's not enough memory.."