Using Running Total in Calculations

[bullius]

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 Result
2017	60	10	600
2016	50	15	750
2015	40	8	320
2014	30	12	360
2013	20	7	140

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!

[v-jiascu-msft]

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]

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]

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]

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]

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]

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

Hi @bullius,

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

Best Regards!

Dale

[v-jiascu-msft]

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]

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. 

[Anonymous]

This should work for you ...

I exactly mimicked your data...

[bullius]

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