Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
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?
Solved! Go to 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]))
)
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!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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!
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!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
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!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
LOL....It was being summed and everything makes perfect sense now!
Thanks!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
84 | |
66 | |
49 |
User | Count |
---|---|
127 | |
109 | |
93 | |
70 | |
67 |