Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
loafers
Frequent Visitor

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. 

 

ProductFlavorSales
JuiceApple50
JuiceOrange40
JuicePineapple30
JuiceStrawberry20
JuiceLemon10
YogurtApple40
YogurtOrange20
YogurtPineapple35
YogurtStrawberry49
YogurtLemon12
1 ACCEPTED SOLUTION

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1701312581519.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5

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

also removefilters() would help too. 

Ashish_Mathur
Super User
Super User

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/

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

ProductFlavorDateSales
JuiceApple1/31/202250
JuiceOrange1/31/202240
JuicePineapple1/31/202230
JuiceStrawberry1/31/202220
JuiceLemon1/31/202210
JuiceApple1/31/202333
JuiceOrange1/31/202338
JuicePineapple1/31/202322
JuiceStrawberry1/31/202348
JuiceLemon1/31/202310
YogurtApple1/31/202240
YogurtOrange1/31/202220
YogurtPineapple1/31/202235
YogurtStrawberry1/31/202249
YogurtLemon1/31/202212
YogurtApple1/31/202331
YogurtOrange1/31/202331
YogurtPineapple1/31/202339
YogurtStrawberry1/31/202345
YogurtLemon1/31/202313

 

The below is a quick pivot of Yogurt by flavor:

ProductFlavor20222023YoY %
YogurtApple4031-23%
YogurtLemon12138%
YogurtOrange203155%
YogurtPineapple353911%
YogurtStrawberry4945-8%
Yogurt Total 1561592%

 

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.

 

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1701312581519.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.