Forum Discussion

HectorMSC's avatar
HectorMSC
Frequent Visitor
3 years ago

Displaying Consistent Row-Level Total in Power BI Table

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 
1Jan   19,573.30   57,674.54
2Feb   25,811.26   57,674.54
3Mar   37,990.13   57,674.54
4Apr   29,146.61   57,674.54
5May   35,710.92   57,674.54
6Jun   47,768.50   57,674.54
7Jul   61,694.75   57,674.54
8Aug  100,907.03   57,674.54
9Sep   85,188.63   57,674.54
10Oct  110,580.10   57,674.54
11Nov   64,185.79   57,674.54
12Dec   73,537.51   57,674.54
Total Sales/Year   692,094.54 
Average Monthly Sales/Year    57,674.54 

 

Need Help

5 Replies

  • parry2k's avatar
    parry2k
    Super User
    3 years ago

    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's avatar
      HectorMSC
      Frequent Visitor
      3 years ago

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

       

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    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's avatar
      HectorMSC
      Frequent Visitor
      3 years ago

      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's avatar
    parry2k
    Super User
    3 years ago

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