cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Prodigy

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

1 ACCEPTED SOLUTION
Super User

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

5 REPLIES 5
Resident Rockstar

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

Proud to be a Super User!

Super User

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

Post Prodigy

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

Super User

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

😉

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

Post Prodigy

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

Thanks!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.