Forum Discussion
Responsive ResidentREMOVEFILTERS() not working as expected
Hi,
I'm trying to create a measure that removes the Customer filter so I can show an overall % sold by part.
In this example for a specific part:
2 customers purchased this part. Total Qty sold = 45
My users want me to show not only the % sold against OH Qty by Customer, but also the OVERALL % sold for that part for all customers.)
They still want to be able to filter by part and by customer, but they want a % column which looks at the overall sold and compares that with the OH Qty.
| CUSTOMER | Part | Qty Sold | |
| AIRBUS | ABS0596S005 | 20 | |
| AIRBUS | ABS0596S005 | 10 | |
| SAFRAN | ABS0596S005 | 5 | |
| SAFRAN | ABS0596S005 | 5 | |
| SAFRAN | ABS0596S005 | 5 | |
| 45 |
The measure I need would return (in this example) Total Sold 45 so I can calculate % OH to Overall Sold (35%)
| Customer | Part | Qty Sold | OH | % OH to Sold to Cust | Total Sold | % OH to Overall Sold |
| Airbus | ABS0596S005 | 30 | 130 | 23.1% | 45 | 35% |
| Safran | ABS0596S005 | 15 | 130 | 11.5% | 45 | 35% |
I tried this measure:
I figured it out. The measure "Total Overall Qty" returns the 45 which is the total sold for that part to ALL customers:
Total Overall Qty =calculate( [Total Qty] ,ALLEXCEPT(Customer, Customer[CUSTOMER] ) )Total Qty = calculate(sumx(Sales, [QUANTITY]) )Customer Mil Spec Qty UOM OH Qty Overall Sold % OH vs Sold SAFRAN ABS0596S005 30 EACH 13 45 28.89%
3 Replies
v-zhangtiCommunity Support
Hi, Roseventura
You can try the following methods.
Total Sold = CALCULATE(SUM('Table'[Qty Sold]),ALL('Table'))% OH to Overall Sold = DIVIDE([Total Sold],SUM('Table'[OH]))At this point[Total Sold] can ignore any filters.
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Roseventura
Unfortunately I tried this approach. ALL() gives me the total Qty of EVERY part for EVERY Customer in the ENTIRE table (see below). I need the measure to give me a total Qty for each part, but for ALL Customers who purchased that part, ignoring any Customer filter.
- Roseventura
I figured it out. The measure "Total Overall Qty" returns the 45 which is the total sold for that part to ALL customers:
Total Overall Qty =calculate( [Total Qty] ,ALLEXCEPT(Customer, Customer[CUSTOMER] ) )Total Qty = calculate(sumx(Sales, [QUANTITY]) )Customer Mil Spec Qty UOM OH Qty Overall Sold % OH vs Sold SAFRAN ABS0596S005 30 EACH 13 45 28.89%