Forum Discussion
INDEX function issue
Good afternoon, everyone!
I'm working on a school project and have a basic task, which turned out very difficult to solve. I have two tables: Sales table (top one) and Shops table used as mapping (below one). There is an active many-to-one relation between them which worked fine up until this task.
The task is to calculate sales deficit from best-selling shop using INDEX function (divide total sales by total sales of the best-selling shop) in a matrix visual. Here is what I got:
In this matrix, rows are hierarchy of cities/shops, column 1 is sum of sales and measure 1 is me trying to calculate the best selling shop. As far as I understand, measure1 should return "Магазин №8" since it's sales are the best. Instead, it just returns "Магазин №1, because it's alphabetically first. What am I doing wrong? This is a measure used:
Thanks everyone for your help.
Please provide sample data that fully covers your issue, in a usable format (no screenshot).
Please show the expected outcome based on the sample data you provided.
9 Replies
You need to specify that you want to order DESC.
hi there! In addition to the order by direction you need to re-define the contest for SUM() using CALCULATE:
measure = INDEX(1, ALLSELECTED('Table'[store]), ORDERBY(CALCULATE(SUM('Table'[sold]), REMOVEFILTERS('Table'[city])), DESC))Please provide sample data that fully covers your issue, in a usable format (no screenshot).
Please show the expected outcome based on the sample data you provided.
