cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors