Forum Discussion
Returning Value of Table Based on Ranking
- 1 year ago
Hi leems101
You can create a calculated colum rank within partitioned by each article and another calculated column for the total views.
Rank = RANKX ( FILTER ( 'Table', 'Table'[Article] = EARLIER ( 'Table'[Article] ) ), 'Table'[ Views], , DESC, DENSE )Total Views by Article = CALCULATE ( SUM ( 'Table'[ Views] ), ALLEXCEPT ( 'Table', 'Table'[Article] ) )And a measure to return concatenated values
My Value = VAR _source = SELECTEDVALUE ( 'Table'[Source] ) & " " VAR _views = SUM ( 'Table'[ Views] ) VAR _percent = FORMAT ( DIVIDE ( _views, CALCULATE ( SUM ( 'Table'[ Views] ), ALLEXCEPT ( 'Table', 'Table'[Source] ) ) ), "0%" ) RETURN _source & FORMAT ( _views, "#,0" ) & " Views " & "(" & _percent & " of total)"Note: Since Total Views and Rank are calculated columns, this approach is not fully dynamic, as calculated columns do not respond to slicer selections. For example, excluding Home using a slicer will not update these values. A more dynamic solution would require a disconnected table and more complex DAX, which involves additional development effort.
Hi leems101
You can create a calculated colum rank within partitioned by each article and another calculated column for the total views.
Rank =
RANKX (
FILTER ( 'Table', 'Table'[Article] = EARLIER ( 'Table'[Article] ) ),
'Table'[ Views],
,
DESC,
DENSE
)
Total Views by Article =
CALCULATE ( SUM ( 'Table'[ Views] ), ALLEXCEPT ( 'Table', 'Table'[Article] ) )
And a measure to return concatenated values
My Value =
VAR _source =
SELECTEDVALUE ( 'Table'[Source] ) & " "
VAR _views =
SUM ( 'Table'[ Views] )
VAR _percent =
FORMAT (
DIVIDE (
_views,
CALCULATE ( SUM ( 'Table'[ Views] ), ALLEXCEPT ( 'Table', 'Table'[Source] ) )
),
"0%"
)
RETURN
_source & FORMAT ( _views, "#,0" ) & " Views " & "(" & _percent & " of total)"
Note: Since Total Views and Rank are calculated columns, this approach is not fully dynamic, as calculated columns do not respond to slicer selections. For example, excluding Home using a slicer will not update these values. A more dynamic solution would require a disconnected table and more complex DAX, which involves additional development effort.