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, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
unclejemima
Post Patron
Post Patron

Report level filter not working on a calculation

I have a report that calculates how much product we have on order of a given product.  To get the results, I have a "SUM" calculation from a different table.

 

I have a calculation that looks like this....

Qty-PO_P&A = calculate(sum('Stok'[QTY-OnSentPO])+calculate(SUM(('Stok'[QTY-OnPendingPO]))))
 
You can see it's getting the calcuation from the table STOK, but this calculation resides in a table called INVENTRY
 
I applied a report level filter to exlude a certain supplier from the report...but it won't change the results of the calculation and I can't seem to figure out why?

Is this normal function, in that a calculation results can not be manipulated by a report level filter?  If so, is there a work around to apply a filter to the calculation?
 
Lets say for example I want to remove SUPPLIER-A from the calculation above.  How would I do that?

Thanks,
11 REPLIES 11
parry2k
Super User
Super User

@unclejemima not sure about your question but you cannot use measures as report or page level filter if that is what you are asking.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

unclejemima
Post Patron
Post Patron

Can I just get clairification on one thing?  Is it supposed to be possible to filter calculations by report level filters?  Yes or no?

 

My filter recognizes that there are 11 matches to the supplier I'm trying to filter.  But when I select this filter for the entire report, the calculations do not change on the report.  

unclejemima_0-1695770476494.png

 

 

I'm trying to figure out if this is by function of the software, or because I have something setup incorrectly.

 

unclejemima
Post Patron
Post Patron

@parry2k  when I asked do you have supplier information in stok table, answer was no,

 

Sorry, I was confused.  You originally asked does stok table have a relationship with the supplier table?

You did not ask if if the supplier information was in the stok table.


I'll try your suggestions and see if I can get it to work.  I apprecaite the help.  I'm not a expert at power BI.  I know just enough to get in trouble 🙂  So thank you for working with me.

@unclejemima Apologies, it is hard to work with little information, sometime you ask the question assuming the person on another side can provide more details. If you think thru my perspective I'm trying to guess the things to give you a solution, more you provide, better and faster the solution. 

 

As I mentioned, do read this when you post future questions, we all here to learn from each other. Best!

 

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@unclejemima 

calculate(sum('Stok'[QTY-OnSentPO]), TabeSupplier[Supplier] <> "Supplier A" )
+
calculate(SUM('Stok'[QTY-OnPendingPO]), TabeSupplier[Supplier] <> "Supplier A" )


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@unclejemima you are making it complicated, when I asked do you have supplier information in stok table, answer was no, if you have a supplier column in stok table, why you cannot create a relationship with the supplier table and then it should work? Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@unclejemima you are trying to perform a calculation based on the model which doesn't support it. First, you need to think outside the Power BI, if you have to perform this calculation manually, how you will do it, and if you are able to solve that then provide the logic. 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Ok.  Thank you @parry2k

 

Can you tell me how to apply a filter to my calculation to remove SUPPLIER-A?  The supplier name exists in the STOK table where the calculation is made so I would assume this is a easy thing to do?

 

Qty-PO_P&A = calculate(sum('Stok'[QTY-OnSentPO])+calculate(SUM(('Stok'[QTY-OnPendingPO]))))

 

 

unclejemima
Post Patron
Post Patron

"@unclejemima does stok table have a relationship with the supplier table?"

 

@parry2k , No it does not.

 

Though I could create a relationship between the STOK and INVENTRY table.  The Supplier name exisits in both those tables.

 

Is there a way to modify the original calulation to filter OUT the SUPPLIER-A

 

This would be an easy solution.

 

 

 

I should also point out, have a LOOKUP formula in the STOK and INVENTRY that pulls the supplier name from the SUPPLIER table.  


IMy filter shows that it finds 11 records of the SUPPLIER-A from the STOK table...but applying the filter does not change the CALCULATE(SUM results


I'm assuming again that this is a design of the CALCULATE(SUM and that the results can not be filtered in a report like I normally would?  This is why I'm looking to apply a change to the calculation so it filters the actual calculation from the get go.

parry2k
Super User
Super User

@unclejemima does stok table have a relationship with the supplier table?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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