Returning Value of Table Based on Ranking

[leems101]

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           Twitter             5

A           Facebook         4

A           Instagram         1

Basically, i want to return a table that looks like

Article  Total Views     Source 1 (Ranking1)                           Source 2 (ranking 2)                      Source 3 (ranking 3)

A           20                 Home 10 Views (50% of total)            Twitter 5 Views (25% of Total)          Facebook 4 views (20% of total)  etc etc

Given there will be mutiple different articles...

ive tried to do this using a rankx function but the rank only works if i add in the source to the table..

can anyone help.

many thanks

J

[danextian · Accepted solution]

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]

@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]

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...