I have encountered a problem when trying to use CALCULATE to find the percentage of an adjusted sales total, of which the adjustment is based on sales type. Attached is a simple sales workbook (data obviously changed for confidentiality) with the Product Data, Sales Data, and Performance tabs. I believe the numbers in the Pct Value of Net Total Sold Amount pivot highlights that the problem lies within the CALCULATE formula for the Net Total Sold Amount. First, it should be noted that I used the exact same formulas for the Total Potential Amount pivot and achieved the desired results, however the difference between the two charts is that different columns are used for each total formula, which are Promotional and Regular sales. Promotional Sales is the sum of the Actual Sold Amount column, while Regular Sales is the sum of the MSRP column; they are the dependent formulas of the Net Total Sold Amount. The results I get in the pivot when sorting the percent values of Net Total Sold Amount by the sales type is 100% for both Promo and Regular sales. This is obviously not correct; they should be roughly somewhere around 50% each. The same result is returned for the dollar amount (Net Total Sold Amount) of the sales type too. Can someone review these formulas to tell me why I am not getting the correct results? And a solution to achieve my correct results?
Bookmarks