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

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.

     

     

     

3 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper 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...

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    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.