Forum Discussion

nhol's avatar
nhol
Advocate II
8 years ago
Solved

Running Total

Hi,   I have 2 years of monthly data on which I'm applying a Running Total measure as written below:   Running Total MEASURE = CALCULATE ( SUM ( CompanyPortalDataBase[PageVisit] ), FILTER ( ...
Data Modeling
Reports
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    8 years ago

    Hi nhol,

     

    Please modify the measure like below: 

     

    Running Total MEASURE =
    CALCULATE (
    SUM ( CompanyPortalDataBase[PageVisit] ),
    FILTER (
    ALL ( CompanyPortalDataBase),
    'CompanyPortalDataBase'[Date] <= MAX ( 'CompanyPortalDataBase'[Date] ) && YEAR(CompanyPortalDataBase[Date]) =YEAR(MAX(CompanyPortalDataBase[Date]))
    )
    )

     

     

    Best Regards,
    Qiuyun Yu 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi,

     

    Try this calculated column formula

     

    =CALCULATE(SUM(CompanyPortalDataBase[PageVisit]),FILTER(CompanyPortalDataBase,CompanyPortalDataBase[Category]=EARLIER(CompanyPortalDataBase[Category])&&CompanyPortalDataBase[Year]=EARLIER(CompanyPortalDataBase[Year])&&CompanyPortalDataBase[Date]<=EARLIER(CompanyPortalDataBase[Date])))/CALCULATE(COUNTROWS(CompanyPortalDataBase),FILTER(CompanyPortalDataBase,CompanyPortalDataBase[Category]=EARLIER(CompanyPortalDataBase[Category])&&CompanyPortalDataBase[Year]=EARLIER(CompanyPortalDataBase[Year])&&CompanyPortalDataBase[Date]<=EARLIER(CompanyPortalDataBase[Date])))

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi,

 

Share some data and show the expected result.

nhol's avatar
nhol
Advocate II
8 years ago

Hi Ashish,

 

Basically I’m trying to achieve the last two columns marked in blue and red.

My database is much larger than the screenshot attached and it has others WebCat and also 2017 data. The date remains a monthly data (I didn't know how to share my PBI here so if needed I can do that as well but I will need some instructions).

 

I want to let my users the ability to drill through from one summary report to a report that show much detail information including this running total and most importantly running average on a monthly basis while filtered by [Year] AND [WebCat] which are the drill through parameters.

 

What DAX formula should I’ll be using to implement this.

 

 

 

Thanks!

Nir H.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    Write this calculated column formula in the Query Editor window

     

    =CALCULATE(SUM(Data[PageVisit]),FILTER(Data,Data[WebCat]=EARLIER(Data[WebCat])&&Data[Year]=EARLIER(Data[Year])&&Data[Date]<=EARLIER(Data[Date])))

     

    This should get you the Running Total.  Let me know if this is correct and we will then get the Running Average.

     

    Hope this helps.

    • nhol's avatar
      nhol
      Advocate II
      8 years ago

      Hi,

       

      For some reason it didn't work, I think it's because my [Date] field that I was showing in the Excel resides in a different table. Without the [Date] like that:

      =CALCULATE(SUM(Data[PageVisit]),FILTER(Data,Data[WebCat]=EARLIER(Data[WebCat])&&Data[Year]=EARLIER(Data[Year])))

      I got the same aggregated number for the entire year on a monthly basis. 

      Is there a way to do it with a measure and not a column?

       

      Thanks!

      Nir H.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        8 years ago

        Hi,

         

        I cannot comment unless i see your file.  Tell me exactly where my result deviates from your expected result.