+ Reply to Thread
Results 1 to 2 of 2

PowerPivot CALCULATE ignoring filter?

  1. #1
    Registered User
    Join Date
    10-20-2015
    Location
    Alabama
    MS-Off Ver
    Excel 2013
    Posts
    29

    Question PowerPivot CALCULATE ignoring filter?

    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?
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    10-20-2015
    Location
    Alabama
    MS-Off Ver
    Excel 2013
    Posts
    29

    Re: PowerPivot CALCULATE ignoring filter?

    Solved. Use the SUMX and FILTER on the columns. E.g. - Promo Sales, =SUMX(FILTER('SalesData',SalesData[Sales Type]="Promo"),[Total Actual Sold Amount]).

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] powerpivot Calculate with the two filters
    By Hassan1977 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 10-14-2016, 04:49 AM
  2. Why do you use Filter(ALL) in time calc in powerpivot
    By stephme55 in forum Excel General
    Replies: 1
    Last Post: 07-13-2016, 03:28 PM
  3. [SOLVED] Filter WITHIN Powerpivot table
    By stephme55 in forum Excel General
    Replies: 2
    Last Post: 07-11-2016, 12:48 PM
  4. PowerPivot/Dax CALCULATE question
    By TK92 in forum Excel General
    Replies: 3
    Last Post: 06-13-2016, 12:33 PM
  5. PowerPivot Help: Organizing Data using a Filter
    By chrischunggg in forum Excel General
    Replies: 3
    Last Post: 06-13-2016, 09:02 AM
  6. How to calculate discount in PowerPivot??
    By mrxlsx in forum Excel General
    Replies: 1
    Last Post: 06-13-2015, 12:16 AM
  7. PowerPivot - how to calculate next month's value?
    By illusionek in forum Excel General
    Replies: 9
    Last Post: 01-28-2015, 09:58 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1