Forum Discussion

bullius's avatar
bullius
Helper V
9 years ago

Using Running Total in Calculations

Hi, I have a measure that calculates the running total of no. of companies. I want to multiply this figure for a given year by another amount, e.g.    Year Running Total Amount Desired Res...
Need Help
bullius's avatar
bullius
Helper V
9 years ago

v-jiascu-msft - good suggestion. I haven't used that function before.

 

Unfortunately, it didn't work in this case. I added all the columns I wanted to be able to filter, excluding the date column. The problem is if I exclude the date column, I only get one result, regardless of the year. And if I include the date column as one I want to be able to filter, we're back to the original problem. 

 

Thanks!

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
9 years ago

Hi bullius,

 

Maybe it's a good idea to create a new measure.

 

Best Regards!

Dale

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    9 years ago

    Hi bullius,

     

    Could you please tell me if your problem was resolved? Could you please share the answer or mark the proper answer if it's convenient for you? That will be a help to the others.

    Best Regards!
    Dale

  • bullius's avatar
    bullius
    Helper V
    9 years ago

    The problem has not been resolved. Here is a rather lengthy explanation as to why (if your interested!)

     

    Basically, I have a Dim_Calendar table, a Dim_Companies table, a Fact_OccasionsComp table (start and end dates for companies) and a Fact_Attendance table.

     

    The desired result is total attendance at events for selected years, so I am multiplying the total number of companies by total attendance for any given year.

     

    To do this, I create a running total of companies (running total of companies started minus companies ended, from the Fact_OccasionsComp table). This is then multiplied by the total attendance for a given year.

     

    I want to filter the desired result by year and company attribute (Dim_Companies table).

     

    THE PROBLEM: as previously stated is that filtering the Dim_Calendar table to give the desired result for a given year filters the Fact_OccasionsComp table, therby filtering the running total.

     

    Creating a table that summarises the running total by year and using this summarised column in the desired result calculation solves this problem, but causes another: I can't filter by company attribute because there isn't a relationship between the Dim_Companies table and the summary table.

     

    THE COMPROMISE: I have had to abandon efforts to filter by year because filtering by company attribute is more of a priority.

     

    Hope that helps. Still open to solutions, but happy with the compromise for now.