Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Solved! Go to Solution.
I figured it out. The measure "Total Overall Qty" returns the 45 which is the total sold for that part to ALL customers:
Customer | Mil Spec | Qty | UOM | OH Qty | Overall Sold | % OH vs Sold | ||||||
SAFRAN | ABS0596S005 | 30 | EACH | 13 | 45 | 28.89% |
I figured it out. The measure "Total Overall Qty" returns the 45 which is the total sold for that part to ALL customers:
Customer | Mil Spec | Qty | UOM | OH Qty | Overall Sold | % OH vs Sold | ||||||
SAFRAN | ABS0596S005 | 30 | EACH | 13 | 45 | 28.89% |
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.
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |