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 query and/or 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:
Code: Select all
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 'dCalendar'[psg_dates] as my row label and then a 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?