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

    • RedJedi_81's avatar
      RedJedi_81
      Regular Visitor
      1 year ago
      DateChannelDaypartRankAverage ViewsShare
      15/01/2025BBC NewsALL DAY1851001.19%
      15/01/2025GB NewsALL DAY2701000.98%
      15/01/2025Sky NewsALL DAY3674000.94%
      14/01/2025BBC NewsALL DAY2765001.12%
      14/01/2025GB NewsALL DAY1779001.15%
      14/01/2025Sky NewsALL DAY3739001.09%
      13/01/2025BBC NewsALL DAY1835001.18%
      13/01/2025GB NewsALL DAY2827001.17%
      13/01/2025Sky NewsALL DAY3717001.01%
      12/01/2025BBC NewsALL DAY11077001.33%
      12/01/2025GB NewsALL DAY3670000.83%
      12/01/2025Sky NewsALL DAY2727000.90%
      11/01/2025BBC NewsALL DAY11000001.38%
      11/01/2025GB NewsALL DAY3682000.94%
      11/01/2025Sky NewsALL DAY2694000.96%
      10/01/2025BBC NewsALL DAY11006001.41%
      10/01/2025GB NewsALL DAY2857001.20%
      10/01/2025Sky NewsALL DAY3670000.94%
      09/01/2025BBC NewsALL DAY11135001.58%
      09/01/2025GB NewsALL DAY2931001.30%
      09/01/2025Sky NewsALL DAY3789001.10%
      08/01/2025BBC NewsALL DAY2929001.25%
      08/01/2025GB NewsALL DAY1995001.33%
      08/01/2025Sky NewsALL DAY3730000.98%
      07/01/2025BBC NewsALL DAY3734001.05%
      07/01/2025GB NewsALL DAY1860001.23%
      07/01/2025Sky NewsALL DAY2774001.10%
      06/01/2025BBC NewsALL DAY3786001.08%
      06/01/2025GB NewsALL DAY1851001.17%
      06/01/2025Sky NewsALL DAY2795001.10%
      05/01/2025BBC NewsALL DAY11061001.27%
      05/01/2025GB NewsALL DAY3706000.85%
      05/01/2025Sky NewsALL DAY2733000.88%
      04/01/2025BBC NewsALL DAY1821001.13%
      04/01/2025GB NewsALL DAY2737001.01%
      04/01/2025Sky NewsALL DAY3722000.99%
      03/01/2025BBC NewsALL DAY2835001.12%
      03/01/2025GB NewsALL DAY1878001.18%
      03/01/2025Sky NewsALL DAY3741000.99%
      02/01/2025BBC NewsALL DAY11012001.33%
      02/01/2025GB NewsALL DAY2735000.97%
      02/01/2025Sky NewsALL DAY3695000.91%
      01/01/2025BBC NewsALL DAY11186001.26%
      01/01/2025GB NewsALL DAY3673000.71%
      01/01/2025Sky NewsALL DAY2979001.04%

      This includes the date range. I created two charts to resolve the issue. One for average, one for selected date. The expected outcome would be in a bar chart, like below:

       

      I'm not sure how to express this within the table.

      • RedJedi_81's avatar
        RedJedi_81
        Regular Visitor
        1 year ago
        DateChannelDaypartRankAverage ViewsShare
        31/01/2025BBC NewsALL DAY1938001.33%
        31/01/2025GB NewsALL DAY3661000.94%
        31/01/2025Sky NewsALL DAY2816001.16%
        30/01/2025BBC NewsALL DAY11020001.52%
        30/01/2025GB NewsALL DAY3947001.41%
        30/01/2025Sky NewsALL DAY2955001.43%
        29/01/2025BBC NewsALL DAY2815001.21%
        29/01/2025GB NewsALL DAY1882001.31%
        29/01/2025Sky NewsALL DAY3695001.04%
        28/01/2025BBC NewsALL DAY2702001.06%
        28/01/2025GB NewsALL DAY1738001.12%
        28/01/2025Sky NewsALL DAY3677001.03%
        27/01/2025BBC NewsALL DAY1805001.16%
        27/01/2025GB NewsALL DAY2799001.15%
        27/01/2025Sky NewsALL DAY3784001.13%
        26/01/2025BBC NewsALL DAY11075001.40%
        26/01/2025GB NewsALL DAY3546000.71%
        26/01/2025Sky NewsALL DAY2843001.10%
        25/01/2025BBC NewsALL DAY1992001.42%
        25/01/2025GB NewsALL DAY3533000.76%
        25/01/2025Sky NewsALL DAY2778001.11%
        24/01/2025BBC NewsALL DAY21058001.38%
        24/01/2025GB NewsALL DAY3761000.99%
        24/01/2025Sky NewsALL DAY11061001.38%
        23/01/2025BBC NewsALL DAY1995001.38%
        23/01/2025GB NewsALL DAY3876001.22%
        23/01/2025Sky NewsALL DAY2949001.32%
        22/01/2025BBC NewsALL DAY1871001.21%
        22/01/2025GB NewsALL DAY3728001.01%
        22/01/2025Sky NewsALL DAY2861001.20%
        21/01/2025BBC NewsALL DAY2848001.27%
        21/01/2025GB NewsALL DAY3679001.02%
        21/01/2025Sky NewsALL DAY1897001.34%
        20/01/2025BBC NewsALL DAY11397001.93%
        20/01/2025GB NewsALL DAY31142001.58%
        20/01/2025Sky NewsALL DAY21216001.68%
        19/01/2025BBC NewsALL DAY11294001.64%
        19/01/2025GB NewsALL DAY3601000.76%
        19/01/2025Sky NewsALL DAY2869001.10%
        18/01/2025BBC NewsALL DAY1840001.15%
        18/01/2025GB NewsALL DAY3539000.74%
        18/01/2025Sky NewsALL DAY2642000.88%
        17/01/2025BBC NewsALL DAY3627000.89%
        17/01/2025GB NewsALL DAY2677000.96%
        17/01/2025Sky NewsALL DAY1691000.98%

         

        Rest of the dates