What I want is to rank products based on a sales metric, and display that metric, sorted ascending by rank on a continuous axis.
The catch is that the fact table is at a lower intersectionality than my desired summary, and I need that summary to be reactive to selections made in slicers in the interface.
More practically, I have a table of Sales. Sales are associated to stores, products, and a calendar (by weeks).
I want to chart, by Product
The RANK of:
Productivity = CALCULATE(SUM(Sales)/DISTICTCOUNT(Stores), 'Product'[SomeFlag] = "Something") along the X (horizontal) axis
Productivity = CALCULATE(SUM(Sales)/DISTICTCOUNT(Stores), 'Product'[SomeFlag] = "Something") along the Y
I've managed to achieve this by creating a table:
,"Avg Sales per Store", DIVIDE([Sum Tot Sls Rtl],[Store Count])
and then creating a calculated column
Avg Sales Rank = RANKX('Productivity', 'Productivity'[Avg Sales per Store])
What it does NOT do, which I need it to, is recalculate the summarized data based on the filter context applied to the fact table. I feel like this should be a relatively simple thing to do... but I can't seem to figure it out.
Bonus points if you can help me make it display a line for more than one week.
It is hard to understand the measure without sample data, so if possible, could you please inform me more detailed information(such as your fact table and product table)? And if you want to sort chart by measure or column’s result, you could try to use below step:
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.