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

v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
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 

nhol's avatar
nhol
Advocate II
8 years ago

This worked just fine... Thank you!

Now I have another challenge that I should incorporate into this measure.

I have another filter which is a text category filter that needs to be taken into consideration otherwise the running total summarize the entire population by year and ignores the category.

The category name is: WebCat

And the values are:

  • HomePage
  • CompanyPage
  • ProductPage
  • AboutPage

 

How can this be incorporated?

 

Thanks in advance!

NirH

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