CONTRIBUTION vs ALL - Scrap % example
- Madeleine Nolan (Unlicensed)
The CONTRIBUTION function allows, for example, scrap reasons to be compared with the % values dependent on filters.
The ALL function allows, for example, scrap reasons to be compared with the % values independent of filters.
To compare the % contribution of specific scrap reasons, splitting the entire contribution within those reasons, use: CONTRIBUTION()
E.g.
Step 1. Create a pivot table with Rows: Scrap Reason and Values: Total Scrap Reason Amount & CONTRIBUTION([Total Scrap Reason Amount])
Step 2. Filter Scrap Reason to only the scrap reasons you want to compare against one another
This sum of the resultant scrap amount percentages (CONTRIBUTION([Total Scrap Reason Amount])) will always be 100%, whether the filter has 4 or 40 reasons.
2. To compare the % contribution of specific scrap reasons with the contribution of each reason against the overall reasons, use: ALL()
E.g.
Step 1. Create a pivot table with Rows: Scrap Reason and Values: Total Scrap Reason Amount & the formula [Total Scrap Reason Amount]/([Total Scrap Reason Amount], ALL([Scrap Reason]))
Note: This formula determines the percentage contribution of a scrap reason amount by dividing that scrap reason amount by a constant denominator. The ALL function maintains the constant denominator regardless of filters applied.
If you filter this widget, the Scrap Reason Amount Contribution column values will not change
Note: See below the Total Amount of All Scrap Reasons column, which displays the denominator value of the contributions calculated in Step 1. This denominator is determined using the formula shown below; ([Total Scrap Reason Amount], ALL([Scrap Reason]))