Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello everyone! Glad to be here.
I'm having a few questions, hope someone could guide me.
In my mind this is not supposed to be hard, but somehow I find it tricky.
What I want is to create a measure or a column that can give me the same value on each row.
The value I need is the sum of every single sales mont of year 2021 (692,094.54) divided by the QTY of months (12).
Grand Total is 57,674.54.
But how do I create a column or measure that can give me this data for each single row?
Table is called "Hoja1", sales is in a column called "Ventas Total" and dates in a column called "Fecha Venta".
Thank you in advance.
2021 | |||
1 | Jan | 19,573.30 | 57,674.54 |
2 | Feb | 25,811.26 | 57,674.54 |
3 | Mar | 37,990.13 | 57,674.54 |
4 | Apr | 29,146.61 | 57,674.54 |
5 | May | 35,710.92 | 57,674.54 |
6 | Jun | 47,768.50 | 57,674.54 |
7 | Jul | 61,694.75 | 57,674.54 |
8 | Aug | 100,907.03 | 57,674.54 |
9 | Sep | 85,188.63 | 57,674.54 |
10 | Oct | 110,580.10 | 57,674.54 |
11 | Nov | 64,185.79 | 57,674.54 |
12 | Dec | 73,537.51 | 57,674.54 |
Total Sales/Year | 692,094.54 | ||
Average Monthly Sales/Year | 57,674.54 |
@HectorMSC every time you are working with dates, you have to train your brain to use date dimension. I have a series of videos talking about time intelligence and the importance of date dimension, if interested, check it out here. youtube.com/playlist?list=PLiYSIjh4cEx2FkuGkngYruS0wm8MYFsoi
👉 Learn Power BI and Fabric - subscribe to our YT channel - @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤️
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi,
This pattern should work
Total = sum(Data[Sales])
Total of all months = calculate([Total],all(Calendar[Month name]))
Average = divide([Total of all months],12)
Hope this helps.
Thanks for the assistance Ashish. I followed up every single step, but at the end it managed to keep the same number on each row but failed to sum correctly. However, this is the first time I try this approach of creating a Calendar Table and I find it interesting, so maybe I did something wrong but definitely going to try this approach again in the future.
@HectorMSC this measure will do it:
Total =
CALCULATE ( SUM ( Table[Column] ), ALLSELECTED ( ) )
👉 Learn Power BI and Fabric - subscribe to our YT channel - @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤️
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thank you, I used your formula and added a Filter for Year and it seems to be alright!
GrandTotalSales2021 =
CALCULATE (
SUM ( Hoja1[Ventas Total] ),
FILTER (
ALLSELECTED ( Hoja1 ),
YEAR ( Hoja1[Fecha Venta] ) = 2021
)
)
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
86 | |
84 | |
66 | |
62 | |
61 |
User | Count |
---|---|
199 | |
120 | |
110 | |
79 | |
69 |