Help Needed - Sort Multiple Table based on value in table A

[Anonymous]

Hi Everyone,

I am working on Power BI Dashboard for Financial Data

Table A: This year budget

Project Name               Category A     Category B     Category C   Total

Marketing X                     0.2                    0.1                    0.3           0.6

Websites ZC                     0.2                    1.0                    0.2           1.4

eCommerce                      0.5                   0.5                    0.2            1.2

Total                                 0.9                    1.6                   0.7             3.2

Table B: Last Year Budget

Project Name               Category A     Category B     Category C   Total

Marketing X                     0.4                    0.2                    0.4           1.0

Websites ZC                     0.5                    0.5                    0.2           1.2

eCommerce                      0.5                   0.7                    0.1            1.3

Total                                 1.4                    1.4                   0.7             3.5

We want Table A sorted based on Highest to Lowest for both column and row and Table B follow Table A

Expected Result:

Table A: This year budget

Project Name               Category B     Category A     Category C   Total

Websites ZC                    1.0                    0.2                    0.2           1.4

eCommerce                     0.5                    0.5                    0.2           1.2

Marketing X                      0.1                   0.2                    0.3           0.6

Total                                 1.6                    0.9                   0.7             3.2

Table B: Last year budget

Project Name               Category B     Category A     Category C   Total

Websites ZC                    0.5                    0.5                    0.2           1.2

eCommerce                     0.7                    0.5                    0.1           1.3

Marketing X                      0.2                   0.4                    0.4           1.0

Total                                 1.4                    1.4                   0.7             3.5

Any thought how we can do this Dynamically?

[v-jingzhang]

Hi @Anonymous 

Currently the matrix visual only supports to sort by Row header and Value field. To sort the column, you need to add a calculated column in the data table to calculate this year's budget in advance, then sort the category column by it. 

Additionally, it is not supported to sort a matrix visual by values in another visual. You may also need to create a calculated column to sort by in advance. Does data in both visuals come from the same data table? Is the value field a measure or a column? Can you please share some dummy data to show the original data table then we can look into it if it is possible to create a calculated column for sorting-by? 

Best Regards,
Community Support Team _ Jing

[Anonymous]

Sure, my raw table consist of 2 sheet. First Sheet is project list, Second sheet is Category and Value like below

Project Name	Category	2021	2022
Websites	X	100	200
Websites	Y	50	10
Project A	X	120	250
Project A	Y	200	400

[amitchandak]

@Anonymous , If this a matrix column, it can only sort on sort column

Assume the data is like

Product Name, Category , Value

Then you can create a new column

Cat value= Sumx(filer(Table, [Category] = earlier([Category])) ,[Value])

Category 1 = [Category]

And mark cat value as sort column for Category 1, and use that in visual

How to Create Sort Column and Solve Related Errors:
https://www.youtube.com/watch?v=KK1zu4MBb-c