Hello everyone,I'm trying to calculate both a daily rank and an all-time average rank in Power BI. Dataset Overview:I have a table in Power Query with the following columns:DateTime of Day (e.g., Breakfast, Morning, Afternoon)Channel (e.g., Sky, BBC, ITV)Views (Whole number)Share (Percentage)Rank (Values 1-3, where 1 represents the highest views on that day, and 3 represents the lowest)I also have a dynamic measure that switches between Views and Share for visualization in charts. What I'm Trying to Achieve:I want to display:The rank on a selected date → (I already have this working).The all-time average views and share (excluding the selected date).An all-time average rank based on the average views (or share) over time.Example Output:For 25/01/2025 (Selected Day):BBC → Views: 25,000 | Rank: 1For the period 20/01/2025 to date (excluding 25/01/2025):BBC → Average Views: 32,000 | Average Rank: 2I’d appreciate any guidance on how to correctly calculate the all-time average views and share (excluding the selected day) and assign an average rank to the channel. Thanks in advance! 😊

Hi RedJedi_81 , To calculate the all-time average views (excluding the selected date) and the all-time average rank in Power BI, you can use DAX measures. First, to calculate the average views while excluding the selected date, you can use the following measure: Avg Views Excluding Selected Date = VAR SelectedDate = SELECTEDVALUE( 'YourTable'[Date] ) RETURN CALCULATE( AVERAGE( 'YourTable'[Views] ), 'YourTable'[Date] <> SelectedDate ) If you are dynamically switching between Views and Share using an existing measure, you can modify the calculation as follows: Avg Metric Excluding Selected Date = VAR SelectedDate = SELECTEDVALUE( 'YourTable'[Date] ) RETURN CALCULATE( AVERAGE( 'YourTable'[Dynamic Metric] ), 'YourTable'[Date] <> SelectedDate ) To determine the all-time average rank based on views, you need to compute the average views per channel and then apply a ranking function. The following measure ranks the channels based on their average views while excluding the selected date: Average Rank = VAR SelectedDate = SELECTEDVALUE( 'YourTable'[Date] ) VAR AvgViewsTable = ADDCOLUMNS( SUMMARIZE( 'YourTable', 'YourTable'[Channel] ), "AvgViews", CALCULATE( AVERAGE( 'YourTable'[Views] ), 'YourTable'[Date] <> SelectedDate ) ) RETURN RANKX( AvgViewsTable, [AvgViews], , ASC, DENSE ) If you need to rank the channels based on average share instead of views, you can adjust the calculation accordingly: Average Rank Share = VAR SelectedDate = SELECTEDVALUE( 'YourTable'[Date] ) VAR AvgShareTable = ADDCOLUMNS( SUMMARIZE( 'YourTable', 'YourTable'[Channel] ), "AvgShare", CALCULATE( AVERAGE( 'YourTable'[Share] ), 'YourTable'[Date] <> SelectedDate ) ) RETURN RANKX( AvgShareTable, [AvgShare], , ASC, DENSE ) With these measures, you can display the selected day's rank along with the all-time average views and corresponding rank in a table visual. For example, if BBC had 25,000 views and was ranked 1 on 25/01/2025, the historical average views (excluding that date) might be 32,000, with an average rank of 2. These calculations provide insight into how a channel's performance compares to its historical trend. Best regards,

Hi DataNinja777, Thank you so much for your response.The issue I'm having is when it comes to selecting my switch metricAvg Metric Excluding Selected Date = VAR SelectedDate = SELECTEDVALUE( 'Date Yesterday Filter'[Date])RETURN CALCULATE( AVERAGE( 'Techedge Dayparts for Top Ranked Days'[Views_Share]), -- I get the error Column 'Views_Share' in table 'Techedge Dayparts for Top Ranked Days' cannot be found or may not be used in this expression. Si I cannot select it-- 'Techedge Dayparts for Top Ranked Days'[Date] <> SelectedDate )For some more context my date selector/filter is in a table Date_and_Periods (column) Date Filter.Date filter has a textb row called yesterday, but there is another regular dd/mm/yyyy column that I can use in the DAX.I really appreciate your help.Thanks again

Apologies the date table in my date selector is in the table Date Yesterday Filter (column) Date Filter with a text row called yesterday (and all other rows are dd/mm/yyyy, but there is another regular dd/mm/yyyy column that I can use in the DAX.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).Do not include sensitive information. Do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided. Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216 Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

DateChannelDaypartRankAverage ViewsShare06/03/2025 00:00BBC NewsDAYTIME1882001.96%06/03/2025 00:00GB NewsDAYTIME3481001.07%06/03/2025 00:00Sky NewsDAYTIME2861001.92%05/03/2025 00:00BBC NewsDAYTIME1952002.08%05/03/2025 00:00GB NewsDAYTIME3802001.75%05/03/2025 00:00Sky NewsDAYTIME2815001.78%04/03/2025 00:00BBC NewsDAYTIME11028002.23%04/03/2025 00:00GB NewsDAYTIME2702001.52%04/03/2025 00:00Sky NewsDAYTIME3592001.28%03/03/2025 00:00BBC NewsDAYTIME1901001.97%03/03/2025 00:00GB NewsDAYTIME2828001.81%03/03/2025 00:00Sky NewsDAYTIME3751001.64%02/03/2025 00:00BBC NewsDAYTIME11811002.88%02/03/2025 00:00GB NewsDAYTIME3483000.77%02/03/2025 00:00Sky NewsDAYTIME21001001.59%01/03/2025 00:00BBC NewsDAYTIME11761003.18%01/03/2025 00:00GB NewsDAYTIME3466000.84% Hi Ibendlin,Here is some sample data. The outcome I would like is:e.g. Date [23/01/2024] = Channel, Views, Share, Rank, Avg. Views, Avg. Share, Avg Rank (based on all days except [23/01/2024].Thanks Paul

Calculating Daily and All-Time Average Rank | Microsoft Fabric Community

13 Replies

DataNinja777
Super User
1 year ago
Hi RedJedi_81 ,

To calculate the all-time average views (excluding the selected date) and the all-time average rank in Power BI, you can use DAX measures. First, to calculate the average views while excluding the selected date, you can use the following measure:

Avg Views Excluding Selected Date = VAR SelectedDate = SELECTEDVALUE( 'YourTable'[Date] ) RETURN CALCULATE( AVERAGE( 'YourTable'[Views] ), 'YourTable'[Date] <> SelectedDate )

If you are dynamically switching between Views and Share using an existing measure, you can modify the calculation as follows:

Avg Metric Excluding Selected Date = VAR SelectedDate = SELECTEDVALUE( 'YourTable'[Date] ) RETURN CALCULATE( AVERAGE( 'YourTable'[Dynamic Metric] ), 'YourTable'[Date] <> SelectedDate )

To determine the all-time average rank based on views, you need to compute the average views per channel and then apply a ranking function. The following measure ranks the channels based on their average views while excluding the selected date:

Average Rank = VAR SelectedDate = SELECTEDVALUE( 'YourTable'[Date] ) VAR AvgViewsTable = ADDCOLUMNS( SUMMARIZE( 'YourTable', 'YourTable'[Channel] ), "AvgViews", CALCULATE( AVERAGE( 'YourTable'[Views] ), 'YourTable'[Date] <> SelectedDate ) ) RETURN RANKX( AvgViewsTable, [AvgViews], , ASC, DENSE )

If you need to rank the channels based on average share instead of views, you can adjust the calculation accordingly:

Average Rank Share = VAR SelectedDate = SELECTEDVALUE( 'YourTable'[Date] ) VAR AvgShareTable = ADDCOLUMNS( SUMMARIZE( 'YourTable', 'YourTable'[Channel] ), "AvgShare", CALCULATE( AVERAGE( 'YourTable'[Share] ), 'YourTable'[Date] <> SelectedDate ) ) RETURN RANKX( AvgShareTable, [AvgShare], , ASC, DENSE )

With these measures, you can display the selected day's rank along with the all-time average views and corresponding rank in a table visual. For example, if BBC had 25,000 views and was ranked 1 on 25/01/2025, the historical average views (excluding that date) might be 32,000, with an average rank of 2. These calculations provide insight into how a channel's performance compares to its historical trend.

Best regards,
- RedJedi_81
  Regular Visitor
  1 year ago
  Hi DataNinja777,
  
  Thank you so much for your response.
  
  The issue I'm having is when it comes to selecting my switch metric
  
  Avg Metric Excluding Selected Date =
  VAR SelectedDate = SELECTEDVALUE( 'Date Yesterday Filter'[Date])
  RETURN
  CALCULATE(
  AVERAGE( 'Techedge Dayparts for Top Ranked Days'[Views_Share]), -- I get the error Column 'Views_Share' in table 'Techedge Dayparts for Top Ranked Days' cannot be found or may not be used in this expression. Si I cannot select it--
  'Techedge Dayparts for Top Ranked Days'[Date] <> SelectedDate
  )
  
  For some more context my date selector/filter is in a table Date_and_Periods (column) Date Filter.
  Date filter has a textb row called yesterday, but there is another regular dd/mm/yyyy column that I can use in the DAX.
  
  I really appreciate your help.
  
  Thanks again
  - RedJedi_81
    Regular Visitor
    1 year ago
    Apologies the date table in my date selector is in the table Date Yesterday Filter (column) Date Filter with a text row called yesterday (and all other rows are dd/mm/yyyy, but there is another regular dd/mm/yyyy column that I can use in the DAX.
RedJedi_81
Regular Visitor
1 year ago
the 23/01/2024 was an example, from the sample 04/03/2025 could be used, e.g. Date [04/03/2025] = Channel, Views, Share, Rank, Avg. Views, Avg. Share, Avg Rank (based on all days except [04/03/2025].
- lbendlin
  Super User
  1 year ago
  I cannot assist you if you are unable to provide meaningful sample data. I hope someone else can help you further.
Anonymous
Not applicable
1 year ago
Hi RedJedi_81,

Thank you for reaching out in Microsoft Community Forum.

Thank you lbendlin , DataNinja777 for the helpful response.

Please provide some sample data, clearly explain the question you're trying to ask, and specify what outcome you expect. if your query is resolved please consider marking it as "Accept as Solution" and give it a 'Kudos'. if it was helpful. help other members find it more easily.

Please continue using Microsoft community forum.

Regards,
Pavan.
Anonymous
Not applicable
1 year ago
Hi RedJedi_81,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please "Accept as Solution" and give a 'Kudos' so other members can easily find it.

Thank you,
Pavan.
Anonymous
Not applicable
1 year ago
Hi RedJedi_81,

I wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.
If the community member's answer your query, please mark it as "Accept as Solution" and select "Yes" if it was helpful.
If you need any further assistance, feel free to reach out.

Thank you,
Pavan.

Forum Discussion

Calculating Daily and All-Time Average Rank

13 Replies

Related content

Calculating Weighted Average

DAX Measure for Ranking the valid rows and calculating the average rating

Calculating average 12 month average

Average Calculation

Average calculation