Forum Discussion

CLewis's avatar
CLewis
Cloud Apps
01-28-2022

Using answer to a calculation within another calculation

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. 

 

Data Models
Finance Analytics

4 Replies

Replies have been turned off for this discussion
  • harikm007's avatar
    harikm007
    Data Warehouse
    01-31-2022

    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's avatar
      CLewis
      Cloud Apps
      02-01-2022

      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's avatar
        Ayantek
        Cloud Apps
        02-10-2022

        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's avatar
    CLewis
    Cloud Apps
    02-10-2022

    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.