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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Roseventura
Responsive Resident
Responsive Resident

REMOVEFILTERS() 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.

 

CUSTOMERPart Qty Sold
AIRBUSABS0596S005 20
AIRBUSABS0596S005 10
SAFRANABS0596S005 5
SAFRANABS0596S005 5
SAFRANABS0596S005 5
   45

 

The measure I need would return (in this example) Total Sold 45 so I can calculate % OH to Overall Sold (35%)

 

CustomerPartQty SoldOH % OH to Sold to CustTotal Sold% OH to Overall Sold
AirbusABS0596S0053013023.1%4535%
SafranABS0596S0051513011.5%4535%

 

I tried this measure:

 

Total Overall Forecast Qty = calculate( [Total Qty] , REMOVEFILTERS( Customer[CUSTOMER] ) )
 
but as soon as I select a customer from the filter, it shows the Qty based on the filtered Customer.
 
What am I missing?
 
Rose
 
1 ACCEPTED SOLUTION
Roseventura
Responsive Resident
Responsive Resident

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%
 
 

View solution in original post

3 REPLIES 3
Roseventura
Responsive Resident
Responsive Resident

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%
 
 
v-zhangti
Community Support
Community 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]))

vzhangti_0-1665727163108.png

At this point[Total Sold] can ignore any filters.

vzhangti_1-1665727186007.pngvzhangti_2-1665727195994.png

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.

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.

 

keepfilters example.jpg

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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