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. 

 

YearRunning TotalAmountDesired Result
20176010600
20165015750
2015408320
20143012360
2013207140

 

This is fine when it is in a table.

 

The problem comes when I want to use a Card visual to show the Desired Result for just one year. I add a visual level filter to select the year and it filters the Running Total measure so that it only counts companies for that year.

 

E.g. If I apply a filter to the card to only include 2017, instead of the Desired Result being 60 * 10 = 600, it calculates 10 (the number of companies with a date in 2017) * 10 = 100.

 

So, is there a way of filtering the desited result, but not the running total.

 

(Bonus points if you can do this by using a single measure, rather than creating additional calculated tables!)

 

Thanks!

Need Help

11 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    This should work for you ...

     

    I exactly mimicked your data...

    • bullius's avatar
      bullius
      Helper V
      9 years ago

      Thanks Anonymous, the problem comes when you use a formula to get the Running Total. 

  • 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