Forum Discussion
Month on Month difference table visual that's sortable
Yes. The limitation you're hitting is that a Power BI Matrix doesn't sort independently by each month column when those months are generated as column groups. The total column can be sorted, but the individual month columns aren't treated as independent sortable fields.
A better approach is to create a flat table visual rather than a Matrix
| Date | Species | Breed | Count |
| ------ | ------- | -------- | ----: |
| 01-Jan | Dog | Labrador | 10 |
| 02-Jan | Dog | Labrador | 15 |
| 01-Feb | Dog | Labrador | 20 |
| 02-Feb | Dog | Labrador | 12 |
First create measures for each month:
January =
CALCULATE(
SUM(Sales[Count]),
MONTH('Date'[Date]) = 1
)
February =
CALCULATE(
SUM(Sales[Count]),
MONTH('Date'[Date]) = 2
)
Then calculate the difference:
MoM Difference =
[February] - [January]
But don't create separate physical columns if you need the solution to work dynamically across years/months.
Better dynamic solution
Use a proper Date table:
DimDate
|
| 1 → *
|
FactTable
Then create:
Current Month =
CALCULATE(
[Total Count],
DATEADD('Date'[Date], 0, MONTH)
)
and:
Previous Month =
CALCULATE(
[Total Count],
DATEADD('Date'[Date], -1, MONTH)
)
Then:
MoM Difference =
[Current Month] - [Previous Month]
For the visual
Use a Table visual:
Species | Breed | Jan | Feb | Mar | Apr | Total
--------------------------------------------------
Dog | Lab | 25 | 32 | 28 | 40 | 125
Dog | Pug | 15 | 19 | 22 | 20 | 76
Cat | Siam. | 30 | 25 | 31 | 35 | 121
However, if you need the user to click Jan, Feb, Mar, etc. and sort the rows by that particular month, a standard Power BI Table/Matrix has limitations when the month columns are dynamic.
Solution SageHi OffColour1972,
A matrix isn't really suited to this if you need every displayed column to be independently sortable. The column headers in your example are part of the matrix structure, so Power BI doesn't treat them like normal table columns for sorting.
If the months are known/fixed, I'd use a Table visual instead and create separate measures for each value you want to display.
For example:
March Total =
CALCULATE(
SUM(Sales[Amount]),
'Date'[Month] = "March"
)
April Total =
CALCULATE(
SUM(Sales[Amount]),
'Date'[Month] = "April"
)
March-April Diff =
[April Total] - [March Total]
Then put Species plus those measures into a Table visual:
Species | March Total | April Total | March-April Diff | May Total | ...
With a Table visual, the user can click the individual column headers and sort by that particular measure.
If the months need to be completely dynamic, that's where it gets more complicated. A matrix is better for dynamically generating the month columns, but you lose some of the straightforward column-by-column sorting you're looking for.
So for a fixed number of months, I'd go with a Table + measures. If the months need to change dynamically based on the data/selection, I'd look at a different design using calculation groups or field parameters.