INDEX function issue

[genashpuryk]

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:

measure1 =

    INDEX(1,ALLSELECTED('рПродажіФакт'[Магазин]),ORDERBY(SUM('рПродажіФакт'[Сума])))

Thanks everyone for your help.

[alena2k]

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

[genashpuryk]

hi! thank you! unfortunately, that didn't really help 😞 
here is what i got using your solution, looks same to me. what can be the source of the problem?

[lbendlin · Accepted solution]

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.

[Anonymous]

Hi @genashpuryk,

As suggested bu super user can you please provide sample data that covers your issue with expected output, it will be helpful for us to address your issue more effectively.

Regards,
Vinay Pabbu

[Anonymous]

Hi @genashpuryk,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Vinay Pabbu

[Anonymous]

Hi @genashpuryk,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Vinay Pabbu

[Anonymous]

Hi @genashpuryk,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Vinay Pabbu

[Anonymous]

Hi @genashpuryk,

As we haven't heard back from you, At this time we are closing this thread. If you have any further issues, please start a new thread in the community forum, and we are here to assist you. Thankyou for your understanding and continuous support.

Thank you for being part of the Microsoft Fabric Community.

Regards,

Vinay Pabbu

[lbendlin]

You need to specify that you want to order DESC.