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,

 

What is your formula of "Running Total"? Maybe you could try to modify the formula like this:

Running Total =
CALCULATE (
    COUNT ( 'table'[company] ),
    FILTER ( ALL ( 'table' ), 'table'[year] <= MAX ( 'table'[year] ) )
)

Best Regards!

Dale

  • bullius's avatar
    bullius
    Helper V
    9 years ago

    Hi,

     

    Here is the formula for the Running Total:

     

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

    It is basically the same as the one you suggested, but with a few differences ("Established" - "Closed", ALLSELECTED, rather than ALL etc...)

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