Forum Discussion
Solved
Sort clustered column chart based on slicer
Relative beginner in PowerBI here. I have this table, showing the last month's profits for a company in several different countries. The next column shows the change of this figure relative to a prev...
Try this solution.
1. Create table SlicerPeriod (no relationships):
2. Create measures:
Change = SUM ( FactTable[Change] )Sort Order = VAR vPeriodSelection = SELECTEDVALUE ( SlicerPeriod[Period] ) VAR vCurrentCountry = SELECTEDVALUE ( FactTable[Country] ) VAR vTableAmount = ADDCOLUMNS ( ALLSELECTED ( FactTable[Country] ), "@Amount", CALCULATE ( [Change], FactTable[Period] = vPeriodSelection ) ) VAR vTableRank = ADDCOLUMNS ( vTableAmount, "@Rank", RANKX ( vTableAmount, [@Amount],, ASC ) ) VAR vResult = MAXX ( FILTER ( vTableRank, FactTable[Country] = vCurrentCountry ), [@Rank] ) RETURN vResult3. Create slicer using SlicerPeriod[Period].
4. Create matrix and add the measure [Sort Order] as a Tooltip (sort Ascending):
5. Add remaining fields to matrix. Legend uses FactTable[Period].
6. Result:
---
Try this solution.
1. Create table SlicerPeriod (no relationships):
2. Create measures:
Change = SUM ( FactTable[Change] )Sort Order =
VAR vPeriodSelection =
SELECTEDVALUE ( SlicerPeriod[Period] )
VAR vCurrentCountry =
SELECTEDVALUE ( FactTable[Country] )
VAR vTableAmount =
ADDCOLUMNS (
ALLSELECTED ( FactTable[Country] ),
"@Amount", CALCULATE ( [Change], FactTable[Period] = vPeriodSelection )
)
VAR vTableRank =
ADDCOLUMNS ( vTableAmount, "@Rank", RANKX ( vTableAmount, [@Amount],, ASC ) )
VAR vResult =
MAXX ( FILTER ( vTableRank, FactTable[Country] = vCurrentCountry ), [@Rank] )
RETURN
vResult
3. Create slicer using SlicerPeriod[Period].
4. Create matrix and add the measure [Sort Order] as a Tooltip (sort Ascending):
5. Add remaining fields to matrix. Legend uses FactTable[Period].
6. Result:
---