Forum Discussion

lcasey's avatar
lcasey
Post Prodigy
6 years ago
Solved

Creating a new table using summerize

Hello,

 

I have a realy weird calculation that doesnt seem to work.  I am creating a new table with 3 columns from an existing table. 

 

The new table has 3 columns

  1. Revenue Gap  - Whole number 
  2. Regularization - Currency 
  3. Average = Currency divided by Gap 

The Revenue Gap and Regularization fields summarize correctly, but yet the Average which is a simple divide does not.   Do you know why and how to fix?

 

Revenue Gap = SUMMARIZECOLUMNS(Revenue[Campaign Name _x002dId],Revenue[Revenue Gap],Revenue[Regularization],"Gap",SUM(Revenue[Revenue Gap]),"Revenue",sum(Revenue[Regularization]),"Average" ,DIVIDE(sum(Revenue[Regularization]),sum(Revenue[Revenue Gap])))
 
Need Help
  • ImkeF's avatar
    ImkeF
    6 years ago

    Hi lcasey ,

     

    including the amount-columns in the columns to aggregate on doesn't seem to make much sense in my eyes. What does the expression return if you omit them like so:

     

     

    Revenue Gap = 
SUMMARIZECOLUMNS(
    Revenue[Campaign Name _x002dId],
    "Gap",SUM(Revenue[Revenue Gap]),
    "Revenue",sum(Revenue[Regularization]),
    "Average" ,DIVIDE(sum(Revenue[Regularization]),sum(Revenue[Revenue Gap]))
)

     

     

5 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    6 years ago

    Hi lcasey ,

     

    including the amount-columns in the columns to aggregate on doesn't seem to make much sense in my eyes. What does the expression return if you omit them like so:

     

     

    Revenue Gap = 
SUMMARIZECOLUMNS(
    Revenue[Campaign Name _x002dId],
    "Gap",SUM(Revenue[Revenue Gap]),
    "Revenue",sum(Revenue[Regularization]),
    "Average" ,DIVIDE(sum(Revenue[Regularization]),sum(Revenue[Revenue Gap]))
)

     

     

    • lcasey's avatar
      lcasey
      Post Prodigy
      6 years ago

      That Worked!

      It does add up the Total a little different as I think the overall average should be 115

       

      Take 3749629 / 32619 = 114.95

       

      • lcasey's avatar
        lcasey
        Post Prodigy
        6 years ago

        LOL....It was being summed and everything makes perfect sense now!

         

        Thanks!

  • JarroVGIT's avatar
    JarroVGIT
    Resident Rockstar
    6 years ago

    Hi lcasey 

    Note that when you create a calculated table, the values are calculated once and are not re-calculated (just like a caluclated column is evaluated once for every row but is not changed by context filters). 

    In your case, we are looking at just a subsection of the columns in your table visual. THe Average column in the Table visual is the sum (probably) of the Average column of your aclculated table per Campaign Name.

    Hope this helps, let me know if you have more questions!

     

    Kind regards

    Djerro123

    -------------------------------

    If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

    Keep those thumbs up coming! 🙂