Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
Hi everyone,
I've been searching for a solution online without success, and I'm starting to think there might not be one.
However, before giving up, I wanted to check if someone here can prove me wrong.
I have a matrix visual in a Power BI report:
I need to sort the matrix ascending or descending based on the values of the measure in a specific week column (so the "DHyyyyww" columns, see screenshot below).
However, Power BI does not allow sorting on measure values in columns directly (by clicking on the headers).
Since clicking on the headers to sort isn't possible, I'm open to alternative solutions, such as allowing users to select the sort column from a dropdown or any other workaround.
But as for now, I'm unable to implement any workaround on my own.
Salesvalue Catalogue =
VAR SelectedCatalogue = SELECTEDVALUE('t_CatalogueSelection'[Catalogue])
RETURN
IF(
NOT(ISBLANK(SelectedCatalogue)),
CALCULATE(
SUM('DWH F_Sales_Catalogue'[TotalRevenueEUR]),
'DWH D_Catalogue'[Catalogue] = SelectedCatalogue,
'DWH F_Sales_Catalogue'[FlagActive] = TRUE()
),
BLANK()
)
Does anyone have an idea how to achieve this? Any workaround that allows sorting dynamically would be greatly appreciated!
Thanks in advance!
Solved! Go to Solution.
Sorry it took so long to respond, @v-venuppu and @amitchandak.
Basically, after much trail and error, I have 2 imperfect solutions for this problem!
Option 1 - Quite cumbersome
SelectedCatalogueSales =
VAR SelectedCatalogue = SELECTEDVALUE('t_CatalogueSelectionSort'[Catalogue])
RETURN
CALCULATE(
SUM('DWH F_Sales_Catalogue'[TotalLineAmount]),
'DWH F_Sales_Catalogue'[D_Catalogue_ID] IN
CALCULATETABLE(
VALUES('DWH D_Catalogue'[D_Catalogue_ID]),
'DWH D_Catalogue'[Catalogue] = SelectedCatalogue,
'DWH D_Catalogue'[FlagActive] = TRUE()
),
'DWH F_Sales_Catalogue'[FlagActive] = TRUE()
)
ShowCatalogueSortSlicer =
VAR CurrentCat = SELECTEDVALUE('t_CatalogueSelectionSort'[Catalogue])
RETURN
IF (
CurrentCat IN VALUES('t_CatalogueSelection'[Catalogue]),
1,
0
)
Downsides of option 1
Option 2 - Hardcode everything
Revenue - DH202301 =
CALCULATE(
SUM('DWH F_Sales_Catalogue'[TotalLineAmount]),
'DWH D_Catalogue'[Catalogue] = "DH202301",
'DWH F_Sales_Catalogue'[FlagActive] = TRUE()
)
Downsides of option 2
Conclusion
I eventually went for option 2 as this was the cleanest approach for the end-user.
However, it does come with a routine weekly update & a big one for each new year...
Microsoft should really add this basic functionality to the matrix visual.
Sorry it took so long to respond, @v-venuppu and @amitchandak.
Basically, after much trail and error, I have 2 imperfect solutions for this problem!
Option 1 - Quite cumbersome
SelectedCatalogueSales =
VAR SelectedCatalogue = SELECTEDVALUE('t_CatalogueSelectionSort'[Catalogue])
RETURN
CALCULATE(
SUM('DWH F_Sales_Catalogue'[TotalLineAmount]),
'DWH F_Sales_Catalogue'[D_Catalogue_ID] IN
CALCULATETABLE(
VALUES('DWH D_Catalogue'[D_Catalogue_ID]),
'DWH D_Catalogue'[Catalogue] = SelectedCatalogue,
'DWH D_Catalogue'[FlagActive] = TRUE()
),
'DWH F_Sales_Catalogue'[FlagActive] = TRUE()
)
ShowCatalogueSortSlicer =
VAR CurrentCat = SELECTEDVALUE('t_CatalogueSelectionSort'[Catalogue])
RETURN
IF (
CurrentCat IN VALUES('t_CatalogueSelection'[Catalogue]),
1,
0
)
Downsides of option 1
Option 2 - Hardcode everything
Revenue - DH202301 =
CALCULATE(
SUM('DWH F_Sales_Catalogue'[TotalLineAmount]),
'DWH D_Catalogue'[Catalogue] = "DH202301",
'DWH F_Sales_Catalogue'[FlagActive] = TRUE()
)
Downsides of option 2
Conclusion
I eventually went for option 2 as this was the cleanest approach for the end-user.
However, it does come with a routine weekly update & a big one for each new year...
Microsoft should really add this basic functionality to the matrix visual.
Hi @JasperN ,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.
Thank you.
Hi @JasperN ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.
Regards,
Rama U.
Hi @JasperN ,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Regards,
Rama U.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
76 | |
75 | |
55 | |
37 | |
33 |
User | Count |
---|---|
99 | |
56 | |
51 | |
44 | |
40 |