Calculations and Filters on Aggregated Measures

[rgopalr]

I have a Sales table, Budget table and a dimension table for Store and Date. 

Sales table:

Date	Store	Sales
1/1/2020	Store 1	20
1/1/2020	Store 2	50
1/1/2020	Store 3	100
1/1/2020	Store 1	80
1/1/2020	Store 3	20

Budget Table:

Date	Store	Budget
1/1/2020	Store 1	50
1/1/2020	Store 2	100
1/1/2020	Store 3	100

The sales table and budget table are connected through DimDate and DimStore using Many to 1 relationship. I have a calculated measure that calculates the variance to budget using the formula VTB = (Sales - Budget)/Budget.

The question is i wanted to get the number of stores that exceeds the budget and have this slicable by Date. For ex. in the insatnce of the above table, Store 1 and Store 3 exceeds the budget for the month so the measure (which i am using to display in a card) should show 2.

When i use the filter on aggregated columns it is filtering the underlying tables and not giving me the right values for the measure. Any help is greatly appreciated.

[Anonymous · Accepted solution]

[# Stores VTB+] =
	countrows(
		filter(
			DimStore,
			[VTB] > 0
		)
	)

Best

D

[rgopalr]

Thanks so much darlove! That worked like a charm!!

[mahoneypat]

I am not clear on your model/relationships.  In your last post you said Sales and Budget tables have relationship on Store and Date?  In any case, assuming your relationships and your VTB measure are working correctly, you could try this approach

Make another measures called Pct Budget that is similar to your VTB but the Return is Sales/Budget instead, then use this measure:

Num Stores Over Budget = var summarytable= addcolumns(summarize(Sales, Sales[Store]), "Budget", [Pct Budget])

return countrows(Filter(summarytable, [Budget]>1))

[Greg_Deckler]

Use ALL or ALLEXCEPT in your measure to override your filters. Could also possibly use REMOVEFILTERS.

[rgopalr]

Thanks Greg_Deckler. I dont want to override the filters. I want the measure (Number of stores exceeding budget) to be slicable by date filters. I have to to some kind of summarize to get to it. But could not figure it out.