June 13, 2017 at 6:58 pm #7969
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?June 13, 2017 at 6:59 pm #7971
Here’s the screen grab of the incorrect Sales Days measure which returns only one (1) since I’m pivoting off individual days.June 13, 2017 at 7:01 pm #7977
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.
You must be logged in to reply to this topic.