- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Measure to show total of all even if filtering out a few items
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 |
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi,
PBI file attached.
Hope this helps.
Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Try to incorprate keepfilters() in your measure, as in calculate(sum(), keepfilters())
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

also removefilters() would help too.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
06-27-2024 02:07 AM | |||
11-21-2023 07:11 PM | |||
01-02-2024 05:20 PM | |||
Anonymous
| 06-21-2024 08:25 AM | ||
12-05-2024 06:10 AM |
User | Count |
---|---|
140 | |
110 | |
81 | |
60 | |
46 |