PowerBi Matrix table - sort by month and value

illusionek

Board Regular
Joined
Jun 21, 2014
Messages
104
Hello

I am using Matrix table and the challenge I am facing is that I want the table to be sorted by Month like in below example starting with lowest value but then I would like data to get sorted by value starting with larges. Based on the below example month 1 and 2 are fine but this is just a coincidence. Now I would like to achieve the same sorting for month 3. I have tried multiple ways and spend ages on google but I am unable to find a solution.

Any ideas?

Link to sample data below

PowerBi Sample.pbix



1680598436533.png
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
I don't believe you can, sadly.
 
Upvote 0
There is way via Power Query. Create a copy of your table and put this M-code in the advanced editor (only change the source line to suit)
It creates a grouped table and add index numbers to the region. (maybe the second step is not necessary at your machine)

Power Query:
let
    source = yourpath,
    rep = Table.ReplaceValue(source,",",".",Replacer.ReplaceText,{"Total Value"}),
    cTypes = Table.TransformColumnTypes(rep,{{"Total Value", type number}}),
    grp = Table.Group(cTypes, {"Region", "OrderDate - Month"}, {{"Aantal", each List.Sum([Total Value])}}),
    sort = Table.Buffer(Table.Sort(grp,{{"OrderDate - Month", Order.Ascending}, {"Aantal", Order.Descending}})),
    grp2 = Table.Group(sort, {"OrderDate - Month"}, {{"Aantal", each Table.AddColumn(Table.AddIndexColumn(_,"index", 1),"Region_t", each Text.From([index]) & "." & [Region])}}),
    expand = Table.ExpandTableColumn(grp2, "Aantal", {"Aantal", "Region_t"})
in
    expand


1680627478448.png
 
Upvote 0

Forum statistics

Threads
1,223,360
Messages
6,171,629
Members
452,412
Latest member
thomasleysen531

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top