Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Hi,
PBI file attached.
Hope this helps.
Try to incorprate keepfilters() in your measure, as in calculate(sum(), keepfilters())
also removefilters() would help too.
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.
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
95 | |
91 | |
82 | |
69 |
User | Count |
---|---|
161 | |
126 | |
121 | |
109 | |
95 |