Forum Discussion
Power BI - Calculate Function - other filters
I have a data set that includes location, product number, product description, and on hand. Looks like this:
| Loc type | Location Code | Product Number | Product Description | UOM | On Hand |
| DIS | 76 | 123 | APPLE | CASE | 5 |
| SUP | 2 | 123 | APPLE | CASE | 15 |
| CON | 35 | 123 | APPLE | CASE | 10 |
| DIS | 74 | 456 | PIE | EACH | 20 |
| SUP | 2 | 456 | PIE | EACH | 20 |
| CON | 35 | 456 | PIE | EACH | 20 |
| DIS | 44 | 456 | PIE | EACH | 20 |
| SUP | 2 | 456 | PIE | EACH | 20 |
| CON | 63 | 456 | PIE | EACH | 20 |
I have then used the Calculate Function to aggreate the data.
| Loc type | UOM | Total |
| CON | CASE | 10 |
| CON | EACH | 40 |
| CON Total | 50 | |
| DIS | CASE | 5 |
| DIS | EACH | 40 |
| DIS Total | 45 | |
| SUP | CASE | 15 |
| SUP | EACH | 40 |
| SUP Total | 55 |
The issue comes in that I want the user to be able to filter on both the Product Number and Product Description seperately. When the user filters on Product Number, the correct data shows because Product Number is in the Calculate function. When the User filters on Product Description, the data is not filtered down, I'm assuming because it is not in the Calculate Function. Is there a way that I can have the Product Description filter without adding it to the calculate function?
Just use it within the measure, that will give you the right totals:
New Measure = CALCULATE( sum('Inventory Viewer UOM'[On Hand]) ,ALLEXCEPT('Inventory Viewer UOM' ,'Inventory Viewer UOM'[Location Code] ,'Inventory Viewer UOM'[Loc Type] ,'Inventory Viewer UOM'[UOM] ,'Inventory Viewer UOM'[Product Number], 'Inventory Viewer UOM'[Product Description] ))I attach you my sample file for your reference
3 Replies
- ray_aramburo
Super User
Just use it within the measure, that will give you the right totals:
New Measure = CALCULATE( sum('Inventory Viewer UOM'[On Hand]) ,ALLEXCEPT('Inventory Viewer UOM' ,'Inventory Viewer UOM'[Location Code] ,'Inventory Viewer UOM'[Loc Type] ,'Inventory Viewer UOM'[UOM] ,'Inventory Viewer UOM'[Product Number], 'Inventory Viewer UOM'[Product Description] ))I attach you my sample file for your reference
Thank you for this response and I agree this answer does work, however I was looking for a way for the filtering to work without putting the column into the Calcuate function. My acutal project has too many fields to include in each of the Calculate functions. Things like Location Name, Location Description, Product Category, Product Supplier, etc etc. I'd like to know if there is a way for the filters to operate on the data without including them in the Calculate function? Or maybe the calculate function is the incorrect way to aggreate this data to make the filtering work the way I need it to?
- ray_aramburo
You don't need to include all of the fields in a CALCULATE function. You can just create a simple SUM column and your visuals and filters will determine your totals. These will change according to the granularity of your visual/table. If you want to go beyond that, you can create field parameters to even filter/let the user select which fields he wants to display.
