Sort by Column - Dynamic Indexing

[Proquation]

Hi there,

Let's say for example I have parameters/columns A, B & C with numerical y-values Y and x-values X. I also have dates setup so that Y sees years 2020-2023 along the X axis. Here's a little snippet: 

I have set up parameter fields to slice my data by different parameters (A, B & C). So when I click on the slicer for A, B or C, Y would change according to that parameter.

My problem is that I would also like the data to be sorted from highest to lowest based on the AVERAGE of Y for that specific X value across the 4 years. Ex. The image above shows that 3872 is on average higher than 4711 across 2020-2023. 

I have created a separate table call it "NEW" with RANKX to rank the average loads by X that links the X values to the original table.

This way, I can sort my Y values by the index by selecting X in table NEW: 

Is there a way so that when I select the parameter of choice, it also sorts the Y values by the index of said parameter?

Thanks for reading through!

[Proquation]

Hi @v-xuxinyi-msft ,

Thank you for your feedback. I have taken these screenshots that will hopefully help a lot more. Please let me know if it isn't enough.

This is my Meter table with the columns Bank Number, MVA, MVAR, LOAD and MW. Averages are calculated as measures.

 Created a parameter field for switching between these different metrics.

 Created a new table to get the indices of the Bank Numbers according to the average metrics to order them in decreasing fashion.

 Currently MVAR's Index is selected and the graph shows the correct decreasing Bank Numbers by the average MVARs.

 Here, I'm changing it from MVAR to LOAD, but MVAR's Index is still active, so I must change it. 

The user has to go to the Bank Number column in Table 3 then select "Sort By" then the parameter that they want to sort by.

Is there a way to avoid this manual process?

Thanks!

[v-xuxinyi-msft]

Hi @Proquation 

Since I don't have your data so I can't reproduce your scenario, could you please provide some sample data and expected results based on sample data? How to provide sample data in the Power BI Forum - Microsoft Fabric Community So that I can help you better. Or show them as screenshots or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account. Thank you for your time and efforts in advance.

Best Regards,
Yulia Xu