Sort each column in matrix independences other columns

[Someone22]

Hello,

Could you please support me?

I have a matrix in Power Bi, which is show the changes of materials.

Currently i have 6 months, July, August, September, October, November, December. The months are dynamic, every cases(date of update) includes 6 months, the intervall change in every next month, so in July the first month will be 2025.August and the last months will be 2026.January. Month is a date format.

So, what i would like to do: sort any column independent the others

for example:

I want to see the TOP increasing in "2025. szeptember" column independent the other columns, but i can not sort any month column, only just the Total column. So, the result what i would like to see is, if i sort descending "2025. szeptember" column, in that column see the decreasing list from the biggest value to the lowest, but in the other column i just see the changes which are not sorted.

For example with values:

Material  2025.július   2025.augusztus   2025.szeptember 2025.október 2025.november 2025.december

A                 100                   200                       800                     150               130                     300

B                  -50                   -100                     750                      50                 80                      900

C                 600                   120                       700                      90                 170                     40

Is that possible to do that? It have to be dynamic, the intervall of month change in every next month.

Thank you in advance!

[v-achippa]

Hi @Someone22,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @mark_endicott and @Rupak_bi for the prompt response.

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user resolved your issue? or let us know if you need any further assistance.
If any response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[mark_endicott]

@Someone22  - the only way to acheive this would be to create a measure for each month, but then your matrix will not be dynamic. 

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

[Someone22]

Hello @mark_endicott ,

I tried it with measures and yes, it is not working in matrix, but i need matrix, so i will do it in separate matzrix.

Thank you!

[mark_endicott]

@Someone22 - thanks, if my suggestion has helped, please mark it as the solution. 

[Rupak_bi]

Hi @Someone22 ,

In matrix, you cannot sort columns. But in table, you can. What you need to do....

1. Make 6 different measure. name like month 1, month 2  ......month 6.

2. the dax will be like 

       month1 = calculate(sum(table,value), table,month = month(today())+1)

       month1 = calculate(sum(table,value), table,month = month(today())+2)...........like this

the drawback will be the month name cannot be seen. 

[Someone22]

Hello @Rupak_bi ,

Sorry for the late answer, but i tried to do it in several ways because i already have a measure for the changes.

So if i create a measure for the months after that i have to create a measure of measures for the changes of between of two date of demands.

The dax what i use for changes: i could not implement your dax to my dax 😞

Demand Change = 
VAR CurrentDate = MAX('YourTable'[Date])
VAR CurrentMonth = SELECTEDVALUE('YourTable'[Month])
VAR CurrentDemand = SUM('YourTable'[Demand])

VAR PrevDate =
    CALCULATE(
        MAX('YourTable'[Date]),
        FILTER(
            ALL('YourTable'),
            'YourTable'[Date] < CurrentDate &&
            'YourTable'[Month] = CurrentMonth
        )
    )

VAR PrevDemand =
    CALCULATE(
        SUM('YourTable'[Demand]),
        'YourTable'[Date] = PrevDate &&
        'YourTable'[Month] = CurrentMonth
    )

RETURN
    IF(ISBLANK(PrevDemand), BLANK(), CurrentDemand - PrevDemand)

Maybe, you have any idea?

Thank you!

[Rupak_bi]

Hi @Someone22 ,

Your ask was to sort individual columns and based on the sample data you shared, I have suggested. However now it looks like you are trying to get some differential. So please share sample data which represents the exact table schema you are working with and also share the desired output. Again , please note, individual columns sorting is not possible in matrix and you need to use a table visual.

[Someone22]

Hello @Rupak_bi  ,

I am really sorry if i was missunderstanding. So, here in my database and steps.

Database: it is dynamic, because the first month will be August and the last month will be 2026.January in July, and so on...

Date Material Material descr Customer Month Base unit Demand
2025.05.28 A AA Company1 2025.Július PC 212
2025.05.28 B BB Company2 2025.Július PC 545
2025.05.28 C CC Company3 2025.Július PC 1124
2025.05.28 A AA Company1 2025.Augusztus PC 1138
2025.05.28 B BB Company2 2025.Augusztus PC 1543
2025.05.28 C CC Company3 2025.Augusztus PC 897
2025.05.28 A AA Company1 2025.Szeptember PC 1601
2025.05.28 B BB Company2 2025.Szeptember PC 886
2025.05.28 C CC Company3 2025.Szeptember PC 1203
2025.05.28 A AA Company1 2025.Október PC 1067
2025.05.28 B BB Company2 2025.Október PC 978
2025.05.28 C CC Company3 2025.Október PC 1942
2025.05.28 A AA Company1 2025.November PC 1346
2025.05.28 B BB Company2 2025.November PC 920
2025.05.28 C CC Company3 2025.November PC 1094
2025.05.28 A AA Company1 2025.December PC 1671
2025.05.28 B BB Company2 2025.December PC 1925
2025.05.28 C CC Company3 2025.December PC 513
2025.06.03 A AA Company1 2025.Július PC 1489
2025.06.03 B BB Company2 2025.Július PC 1175
2025.06.03 C CC Company3 2025.Július PC 346
2025.06.03 A AA Company1 2025.Augusztus PC 117
2025.06.03 B BB Company2 2025.Augusztus PC 950
2025.06.03 C CC Company3 2025.Augusztus PC 1496
2025.06.03 A AA Company1 2025.Szeptember PC 389
2025.06.03 B BB Company2 2025.Szeptember PC 831
2025.06.03 C CC Company3 2025.Szeptember PC 189
2025.06.03 A AA Company1 2025.Október PC 1447
2025.06.03 B BB Company2 2025.Október PC 184
2025.06.03 C CC Company3 2025.Október PC 1052
2025.06.03 A AA Company1 2025.November PC 1511
2025.06.03 B BB Company2 2025.November PC 136
2025.06.03 C CC Company3 2025.November PC 1047
2025.06.03 A AA Company1 2025.December PC 684
2025.06.03 B BB Company2 2025.December PC 422
2025.06.03 C CC Company3 2025.December PC 653
2025.06.06 A AA Company1 2025.Július PC 990
2025.06.06 B BB Company2 2025.Július PC 1264
2025.06.06 C CC Company3 2025.Július PC 1123
2025.06.06 A AA Company1 2025.Augusztus PC 1425
2025.06.06 B BB Company2 2025.Augusztus PC 1153
2025.06.06 C CC Company3 2025.Augusztus PC 539
2025.06.06 A AA Company1 2025.Szeptember PC 1595
2025.06.06 B BB Company2 2025.Szeptember PC 1038
2025.06.06 C CC Company3 2025.Szeptember PC 1345
2025.06.06 A AA Company1 2025.Október PC 1327
2025.06.06 B BB Company2 2025.Október PC 911
2025.06.06 C CC Company3 2025.Október PC 684
2025.06.06 A AA Company1 2025.November PC 1827
2025.06.06 B BB Company2 2025.November PC 299
2025.06.06 C CC Company3 2025.November PC 234
2025.06.06 A AA Company1 2025.December PC 1205
2025.06.06 B BB Company2 2025.December PC 1130
2025.06.06 C CC Company3 2025.December PC 1492
2025.06.15 A AA Company1 2025.Július PC 571
2025.06.15 B BB Company2 2025.Július PC 1154
2025.06.15 C CC Company3 2025.Július PC 615
2025.06.15 A AA Company1 2025.Augusztus PC 857
2025.06.15 B BB Company2 2025.Augusztus PC 1078
2025.06.15 C CC Company3 2025.Augusztus PC 211
2025.06.15 A AA Company1 2025.Szeptember PC 1615
2025.06.15 B BB Company2 2025.Szeptember PC 1595
2025.06.15 C CC Company3 2025.Szeptember PC 1735
2025.06.15 A AA Company1 2025.Október PC 685
2025.06.15 B BB Company2 2025.Október PC 1889
2025.06.15 C CC Company3 2025.Október PC 1608
2025.06.15 A AA Company1 2025.November PC 466
2025.06.15 B BB Company2 2025.November PC 975
2025.06.15 C CC Company3 2025.November PC 1662
2025.06.15 A AA Company1 2025.December PC 363
2025.06.15 B BB Company2 2025.December PC 291
2025.06.15 C CC Company3 2025.December PC 1319
2025.06.23 A AA Company1 2025.Július PC 1163
2025.06.23 B BB Company2 2025.Július PC 1274
2025.06.23 C CC Company3 2025.Július PC 434
2025.06.23 A AA Company1 2025.Augusztus PC 1264
2025.06.23 B BB Company2 2025.Augusztus PC 1406
2025.06.23 C CC Company3 2025.Augusztus PC 1269
2025.06.23 A AA Company1 2025.Szeptember PC 571
2025.06.23 B BB Company2 2025.Szeptember PC 1415
2025.06.23 C CC Company3 2025.Szeptember PC 1152
2025.06.23 A AA Company1 2025.Október PC 1833
2025.06.23 B BB Company2 2025.Október PC 403
2025.06.23 C CC Company3 2025.Október PC 1135
2025.06.23 A AA Company1 2025.November PC 1106
2025.06.23 B BB Company2 2025.November PC 425
2025.06.23 C CC Company3 2025.November PC 442
2025.06.23 A AA Company1 2025.December PC 869
2025.06.23 B BB Company2 2025.December PC 900
2025.06.23 C CC Company3 2025.December PC 907

First: i had to create a matrix where we can see the changes between the consecutive dates. So the row is the date, the column is the month and the value is the dax.

The dax:

Demand Change = 
VAR CurrentDate = MAX('YourTable'[Date])
VAR CurrentMonth = SELECTEDVALUE('YourTable'[Month])
VAR CurrentDemand = SUM('YourTable'[Demand])

VAR PrevDate =
    CALCULATE(
        MAX('YourTable'[Date]),
        FILTER(
            ALL('YourTable'),
            'YourTable'[Date] < CurrentDate &&
            'YourTable'[Month] = CurrentMonth
        )
    )

VAR PrevDemand =
    CALCULATE(
        SUM('YourTable'[Demand]),
        'YourTable'[Date] = PrevDate &&
        'YourTable'[Month] = CurrentMonth
    )

RETURN
    IF(ISBLANK(PrevDemand), BLANK(), CurrentDemand - PrevDemand)

Second: after that i had to create a matrix where we can see the changes based on the materials, where the changes of dax calculate the changes between the two latest date. So, the row is the materials, the columns is the month and the value is the new dax.

New dax: similar the above, only the CurrentDemand VAR the difference.

Demand Change_type = 
VAR CurrentDate = MAX('YourTable'[Date])
VAR CurrentMonth = SELECTEDVALUE('YourTable'[Month])

VAR Currentdemand = 
CALCULATE(
    SUM(SD1006_OoH[OoH]),
    SD1006_OoH[Date] = CurrentDate &&
    SD1006_OoH[Month] = CurrentMonth
)

VAR PrevDate =
    CALCULATE(
        MAX('YourTable'[Date]),
        FILTER(
            ALL('YourTable'),
            'YourTable'[Date] < CurrentDate &&
            'YourTable'[Month] = CurrentMonth
        )
    )

VAR PrevDemand =
    CALCULATE(
        SUM('YourTable'[Demand]),
        'YourTable'[Date] = PrevDate &&
        'YourTable'[Month] = CurrentMonth
    )

RETURN
    CurrentDemand - PrevDemand

Third: In the end, i would like to create a matrix or table where i can sort any column independence the others. I could create it in separate matrix, where i can give a visual filter which is a custom column include index where i can choose the exact number and i can sort that one column. It is dynamic.

Future Month Index = 
VAR vToday = TODAY()
VAR vNextMonthStart = EOMONTH(vToday, 0) + 1
VAR vRowDate = SD1006_OoH[Month]

RETURN
IF(
    vRowDate >= vNextMonthStart,
    (YEAR(vRowDate) - YEAR(vNextMonthStart)) * 12 + MONTH(vRowDate) - MONTH(vNextMonthStart) + 1,
    BLANK()
)

It is almost ok, but not the target what i want. In the third step if i sort one of them matrix, i can not see that demand change of materials in the other month. I need to filter the others.

But, i need matrix or table where i sort the changes in any month column, paralell i see the changes of material in the other month. Why it is important? Because if we can see a really big increasing in the first step matrix in September, i can sort the Third step matrix/table and i can see that the increasing is one time or occur in other month(--> maybe system/report problem or the customer upload something wrong) or there is a same decreasing in the other month so it is not problem, just the customer moved it from other month to September.

I hope i can wrote it clear 🙂

Thank you!

[v-achippa]

Hi @Someone22,

Thank you for the detailed explanation. Power bi matrix visuals do not support column wise sorting i.e sorting individual value columns. So it is technically not possible to sort one column and keep all other months unsorted in a single matrix.

After analysing your use case thoroughly, here is the best possible approach:

• Use a Table visual instead of a matrix.
• You have already created a dynamic column like Future Month Index, this will correctly limits the data to the next 6 months from the current month, keep using it as a filter or slicer.
• And also you have implemented a measure that calculates Demand Change between the latest two dates for each material and month, that is what exactly needed here.
• In the Table visual add Material, Month and Demand Change.

You can now sort by any month and still see the change values for that material in all months.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Anjan Kumar Chippa