Creating a new table using summerize

[lcasey]

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])))

[JarroVGIT]

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! 🙂

[ImkeF · Accepted solution]

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]

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

[ImkeF]

Yes, that total row isn't the result of the SUMMARIZECOLUMN-function, but the standard-aggregation of the table visual you're using.

 😉

[lcasey]

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

Thanks!