Home Forums Power Pivot Removing Certain Filter Context Using ALL()

This topic contains 2 replies, has 1 voice, and was last updated by  CarbonChauvinist 6 months ago.

Viewing 3 posts - 1 through 3 (of 3 total)
  • Author
    Posts
  • #7969

    CarbonChauvinist
    Participant
    • Started: 2
    • Replies: 4
    • Total: 6

    I’ve been tasked with putting together a report that lists the top N sales days in a given time period which I’ve been able to do successfully. This is a simple report with only two tables, the fact sales table (fSales) and a calendar table (dCalendar) (see attached screen grab for reference).

    I’m simply trying to create a measure that shows the total number of sales days for the entire year for the top sales days shown and I’m struggling. I know that one would need to use the ALL() function to remove filter context from a given measure, but I can’t figure out how in the world to make it work.

    For instance the top sales day in my example is 5/30/2015. The measure I’ve created for counting the number of sales days is fairly straight forward:

    Number of Sales Days in Year:=DISTINCTCOUNT(‘fSales'[creation_date])

    That measure gives me the correct number of sales days in the fSales table which I’ve verified by filtering the fSales tables to single years (i.e. 2015) and verifying the distinct count is correct.

    Now, when creating the pivot table report I simply use the date columns from the ‘dCalendar’ table as my row labelĀ and then the measure I’ve created for TotalSales in the values section. All good so far.

    The problem becomes the “Number of Sales Days in Year” measure when dragged into this pivot table only returns a value of one (1) for each row since I’m pivoting off individual days (see screen grab). It does work correctly if I use the Year column from the dCalendar table as my rows (see screen grab)

    How would I use the ALL() function to remove the filter context provided on the month and day, therefore returning an accurate DISTINCTCOUNT() of all the ‘fSales'[creation_date] values for the year?

    Attachments:
    You must be logged in to view attached files.
    #7971

    CarbonChauvinist
    Participant
    • Started: 2
    • Replies: 4
    • Total: 6

    Here’s the screen grab of the incorrect Sales Days measure which returns only one (1) since I’m pivoting off individual days.

    Attachments:
    You must be logged in to view attached files.
    #7977

    CarbonChauvinist
    Participant
    • Started: 2
    • Replies: 4
    • Total: 6

    And lastly, here’s the screen grab showing the measure works properly when pivoting off years from the calendar table. I just need to figure out to remove the filters so that the “Number of Sales Days in Year” returns the accurate number for the entire year and not just limited to the individual day in the row label of the pivot table.

    Attachments:
    You must be logged in to view attached files.
Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic.