Forum Discussion

RedJedi_81's avatar
RedJedi_81
Regular Visitor
1 year ago

Calculating Daily and All-Time Average Rank

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: Date Time of Da...
Need Help
RedJedi_81's avatar
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's avatar
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.

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    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

    • RedJedi_81's avatar
      RedJedi_81
      Regular Visitor
      1 year ago
      DateChannelDaypartRankAverage ViewsShare
      06/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  

      • lbendlin's avatar
        lbendlin
        Super User
        1 year ago

        Your sample only has DAYTIME numbers and does not have data for 23/01/2024 .  

         

        Please provide sample data that fully covers your issue.
        Please show the expected outcome based on the sample data you provided.