Using answer to a calculation within another calculation

[CLewis]

Im looking to use the answer from one calculation in another calculation on a separate widget with different filter set up. 

This is my first calculation, it counts how many sales we have prior to drafting for each model and divides by the total number of sales. This then shows the percent sold prior to drafting for each of our models. 

(count([UniqueJob]), [SoldPriorToDrafting], [Years in FinalSaleDate]) / (count([UniqueJob]), [Years in FinalSaleDate])

The problem is I don't want the calculation to run first on the new widget I just need to use the number from the pivot table above as the other widget shows each individual job within the model.  The calculation doesnt work when looking at it in this view. 

[harikm007]

@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?

[CLewis]

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

[Ayantek]

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. 

[CLewis]

Thanks for the replies. We actually went a different route to come up with a solution and created a new column in SQL with the first calculation.