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
v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
9 years ago

Hi bullius,

 

Did you use any slicers or filters?

 

Could you please post a sample in text mode? According to my limited test, the formula should work.

 

Could you please replace "ALLSELECT" with "ALL"? Just for test.

 

Best Regards!

Dale

bullius's avatar
bullius
Helper V
9 years ago

Dim_Calendar is connected to Fact_OccasionsComp. So, if I filter Dim_Calendar, it filters Fact_OccasionsComp, which affects the running total formula because I am using ALLSELECTED, rather than ALL. However, when I use ALL, it bypasses the other filters I have in the formula, ie,

Fact_OccasionsComp[CompOccasion] = "Established"
Fact_OccasionsComp[CompOccasion] = "Closed",
	Fact_OccasionsComp[Date] = null

which changes the result.

If you can think of a way to do this using a measure, that would be useful still, but I think I am going to resort to creating a separate table summarizing the running total by year, then use that to calculate my "Desired Result" column.

 

Thanks for your help!

 

 

 

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

    Hi bullius,

     

    I wonder if "Allexcept" would help. Maybe you could try this one if no other filter exists.

    Running Total =
CALCULATE (
    DISTINCTCOUNT ( Fact_OccasionsComp[CompanyID] ),
    Fact_OccasionsComp[CompOccasion] = "Established",
    FILTER (
        ALLEXCEPT ( Fact_OccasionsComp, Fact_OccasionsComp[CompOccasion] ),
        Fact_OccasionsComp[Date] <= MAX ( Dim_Calendar[Date] )
    )
)
    - CALCULATE (
        DISTINCTCOUNT ( Fact_OccasionsComp[CompanyID] ),
        Fact_OccasionsComp[CompOccasion] = "Closed",
        FILTER (
            ALLEXCEPT ( Fact_OccasionsComp, Fact_OccasionsComp[CompOccasion] ),
            Fact_OccasionsComp[Date] <= MAX ( Dim_Calendar[Date] )
                && Fact_OccasionsComp[Date] = null
        )
    )

    Best Regards!

    Dale

    • 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