Measure to show total of all even if filtering out a few items

[loafers]

Hi,

If I have data like below where I want to show the year over year % increase/decrease in a matrix, how do I write the measure so that even if I filter out a few items the total will still be the total? In data below I want 'Product' to be in a matrix rows and for Flavor I only want to show Apple and Orange in visual. However for total line i want to see the sum of the other flavors as well. 

Product	Flavor	Sales
Juice	Apple	50
Juice	Orange	40
Juice	Pineapple	30
Juice	Strawberry	20
Juice	Lemon	10
Yogurt	Apple	40
Yogurt	Orange	20
Yogurt	Pineapple	35
Yogurt	Strawberry	49
Yogurt	Lemon	12

[learninPBI5Hard]

Try to incorprate keepfilters() in your measure, as in calculate(sum(), keepfilters())

[learninPBI5Hard]

also removefilters() would help too. 

[Ashish_Mathur]

Hi,

To see y-o-y growth, you must have a Date column in your table.  Why is that absent?  Your requirement of showing the total is not clear.  Share relevant data and show the expected result clearly.

[loafers]

Sorry about that, see below for updated data with date.

Product	Flavor	Date	Sales
Juice	Apple	1/31/2022	50
Juice	Orange	1/31/2022	40
Juice	Pineapple	1/31/2022	30
Juice	Strawberry	1/31/2022	20
Juice	Lemon	1/31/2022	10
Juice	Apple	1/31/2023	33
Juice	Orange	1/31/2023	38
Juice	Pineapple	1/31/2023	22
Juice	Strawberry	1/31/2023	48
Juice	Lemon	1/31/2023	10
Yogurt	Apple	1/31/2022	40
Yogurt	Orange	1/31/2022	20
Yogurt	Pineapple	1/31/2022	35
Yogurt	Strawberry	1/31/2022	49
Yogurt	Lemon	1/31/2022	12
Yogurt	Apple	1/31/2023	31
Yogurt	Orange	1/31/2023	31
Yogurt	Pineapple	1/31/2023	39
Yogurt	Strawberry	1/31/2023	45
Yogurt	Lemon	1/31/2023	13

The below is a quick pivot of Yogurt by flavor:

Product	Flavor	2022	2023	YoY %
Yogurt	Apple	40	31	-23%
Yogurt	Lemon	12	13	8%
Yogurt	Orange	20	31	55%
Yogurt	Pineapple	35	39	11%
Yogurt	Strawberry	49	45	-8%
Yogurt Total		156	159	2%

What I want to show in a matrix are just the flavors 'Apple', 'Lemon', and 'Orange' as well as the 2% YoY growth for total Yogurt product. Is there a way to write a measure to make that possible? I would need measure to keep the total as total even if I filter out a few flavors.

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.