Forum Discussion

leems101's avatar
leems101
Frequent Visitor
1 year ago
Solved

Returning Value of Table Based on Ranking

Hi all   just trying to figure out something basic which is racking my brain..   i have a simple table  eg:   Article  Source            Views A           Home             10 A           Twit...
Need Help
  • danextian's avatar
    danextian
    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.

     

     

     

bhanu_gautam's avatar
bhanu_gautam
Super User
1 year ago

leems101 First, create a calculated column to get the total views for each article.

Total Views = CALCULATE(SUM('Table'[Views]), ALLEXCEPT('Table', 'Table'[Article]))

 

Next, create a calculated column to rank the sources based on the number of views for each article.

Rank = RANKX(FILTER('Table', 'Table'[Article] = EARLIER('Table'[Article])), 'Table'[Views], , DESC, DENSE)

 

Then, create a calculated column to calculate the percentage of total views for each source.

Percentage of Total = DIVIDE('Table'[Views], 'Table'[Total Views])

 

inally, create a pivot table to display the data in the desired format. You can use the matrix visualization in Power BI for this purpose.

Place Article in the Rows.
Place Total Views in the Values.
Place Source in the Columns.
Place Views and Percentage of Total in the Values.

  • leems101's avatar
    leems101
    Frequent Visitor
    1 year ago

    thank you for the quick reply. the Earlier section of the rank is returning and error....Earlier /Earliest refers to an erlier row context which doesnt exist...