Displaying Consistent Row-Level Total in Power BI Table

[HectorMSC]

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

[parry2k]

@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! ❤️

[Ashish_Mathur]

Hi,

This pattern should work

1. Create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name by the Month number
2. Create a relationship (Many to One and Single) from the Date column of the Data Table to the Date column of the Calendar Table
3. Drag Year from the Calendar Table to the slicer and select 2021
4. To your matrix visual, drag Month name from the Calendar Table
5. Write these measures

Total = sum(Data[Sales])

Total of all months = calculate([Total],all(Calendar[Month name]))

Average = divide([Total of all months],12)

Hope this helps.

[HectorMSC]

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.

[parry2k]

@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! ❤️

[HectorMSC]

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