CLewis ,
If I'm understanding the problem correctly, you can use 'ALL' function which will ignore the filters/scope of a dimension.
Try this formula in second widget which will give you the same numbers as in first screenshot.
(count([UniqueJob]), [SoldPriorToDrafting], [Years in FinalSaleDate], ALL(JobNo)) / (count([UniqueJob]), [Years in FinalSaleDate], ALL(JobNo))
Is that what you are looking for?
No this gives me the same result as I have without ALL added in. Its sounds like this should be an option but maybe its the placement off ALL at the end of my filters causing an issue? I am not sure.
It is still calculating on a row level so my job 9761 is counted as 1/1 instead of counting all accolade models sold prior to drafting which should be 9/ 13 total Accolade models sold
Hi CLewis ,
I have faced a similar kind of use case to bring count and sum excluding a category in the pivot.
Below is the analysis:
For SUM its a straightforward with ALL function but for count, we need to check for all the filters we added in both the widgets, attaching a few screenshots for your reference.
this is the formula I used to get %percentage (([# of unique Brand ID],All([Gender]))/([# of unique Visit ID],All([Gender])))
In your case, you need to add JobID in All(JobID) both in numerator and denominator.
Hope this helps.
Ayantek.
