Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello! This is actually related to a previous question.
This is how the data looks.
Category | Type | Score | Month | Ranking |
A | 1 | 100 | Jan 2021 | 1 |
A | 2 | 50 | Jan 2021 | 2 |
B | 3 | 75 | Jan 2021 | 1 |
B | 1 | 80 | Feb 2021 | 2 |
B | 3 | 90 | Feb 2021 | 1 |
C | 2 | 60 | Feb 2021 | 1 |
C | 3 | 55 | Mar 2021 | 1 |
C | 1 | 40 | Mar 2021 | 2 |
Now, I put the data into a matrix with the Category and Type as rows, Month as the columns, and Score/Ranking as the values (I plan to use a field parameter to switch between the two). It looks like this:
Category | Type | Jan 2021 | Feb 2021 | Mar 2021 |
A | 1 | 100 | ||
2 | 50 | |||
B | 1 | 80 | ||
3 | 75 | 90 | ||
C | 1 | 55 | ||
2 | 60 | |||
3 | 40 |
What I want to do now is to be able to sort by Score (or Rank, but that's essentially the same thing) for a specific month. For example, if I want to sort by February:
Category | Type | Jan 2021 | Feb 2021 (sort) | Mar 2021 |
A | 1 | 100 | ||
2 | 50 | |||
B | 3 | 75 | 90 | |
1 | 80 | |||
C | 2 | 60 | ||
1 | 55 | |||
3 | 40 |
If it is relevant, Score is a regular column while Ranking a measure. Category, Type, and Month are regular columns as well. Thank you in advance!
Solved! Go to Solution.
Hi @DaxPadawan ,
Please create two measures:
_Score =
VAR _sum =
CALCULATE ( SUM ( 'Table'[Score] ), FILTER ( 'Table', MONTH ( [Month] ) = 2 ) )
RETURN
IF ( ISINSCOPE ( 'Table'[Month] ), SUM ( 'Table'[Score] ), _sum )
_Rank =
VAR _rank =
RANKX (
FILTER (
ALL ( 'Table' ),
[Category] = SELECTEDVALUE ( 'Table'[Category] )
&& [Month] = SELECTEDVALUE ( 'Table'[Month] )
),
CALCULATE ( SUM ( [Score] ) ),
,
DESC,
DENSE
)
VAR feb_rank =
MAXX ( FILTER ( 'Table', MONTH ( 'Table'[Month] ) = 2 ), _rank )
RETURN
IF ( ISINSCOPE ( 'Table'[Month] ), _rank, feb_rank )
Create a field parameter:
You will see:
You can sort by February:
If you don't want to show column subtotals, you can switch off subtotals after sorting:
Best regards,
Yadong Fang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @DaxPadawan ,
Please create two measures:
_Score =
VAR _sum =
CALCULATE ( SUM ( 'Table'[Score] ), FILTER ( 'Table', MONTH ( [Month] ) = 2 ) )
RETURN
IF ( ISINSCOPE ( 'Table'[Month] ), SUM ( 'Table'[Score] ), _sum )
_Rank =
VAR _rank =
RANKX (
FILTER (
ALL ( 'Table' ),
[Category] = SELECTEDVALUE ( 'Table'[Category] )
&& [Month] = SELECTEDVALUE ( 'Table'[Month] )
),
CALCULATE ( SUM ( [Score] ) ),
,
DESC,
DENSE
)
VAR feb_rank =
MAXX ( FILTER ( 'Table', MONTH ( 'Table'[Month] ) = 2 ), _rank )
RETURN
IF ( ISINSCOPE ( 'Table'[Month] ), _rank, feb_rank )
Create a field parameter:
You will see:
You can sort by February:
If you don't want to show column subtotals, you can switch off subtotals after sorting:
Best regards,
Yadong Fang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@DaxPadawan , if this a matrix visual, then you sort on the row total on values or on row columns
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |