cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
actroyani
Regular Visitor

Sum values by category and by month

Hi, I have a dataset where I have data for, let's say, people and their amount of sales. This data is per month and per year. I need to filter the total of their sales by month (of that year) and put this in a new column (Total per month).

 

DatePersonSalesTotal per month
20/12/2020A$10$30
30/12/2020A$20$30
30/12/2020B$5$5
15/01/2021A$7$7
16/01/2021B$5$25
17/01/2021B$20$25

 

I'm currently working with:

 

"Total per month = CALCULATE ( SUM ( Table1[Sales] ), ALLEXCEPT( Table1 , Table1[Person] ))"

 

but this considers sales for every month and year, so what can I add for this to result in the table written above? Thanks in advance.

 
1 ACCEPTED SOLUTION
smpa01
Super User
Super User

@actroyani  you can try this

 

Column =
VAR _0 =
    ADDCOLUMNS (
        ADDCOLUMNS ( tbl, "year", YEAR ( tbl[Date] ) ),
        "month", MONTH ( tbl[Date] )
    )
VAR _1 =
    ADDCOLUMNS (
        _0,
        "test",
            SUMX (
                FILTER (
                    _0,
                    [Person] = EARLIER ( [Person] )
                        && [year] = EARLIER ( [year] )
                        && [month] = EARLIER ( [month] )
                ),
                [Sales]
            )
    )
RETURN
    MAXX (
        FILTER ( _1, [Person] = EARLIER ( tbl[Person] ) && [Date] = EARLIER ( [Date] ) ),
        [test]
    )

 

 

smpa01_0-1636481961129.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


View solution in original post

2 REPLIES 2
v-zhangti
Community Support
Community Support

Hi, @actroyani 

 

  1. Add month and year to calculation column.

 

Year = YEAR('Table'[Date])

Month = MONTH('Table'[Date])

 

vzhangti_0-1636681337437.png

 

   2. Calculate the sum of sales based on the filter of year, month and person.

 

Total per month =
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER (
        'Table',
        'Table'[Person] = EARLIER ( 'Table'[Person] )
            && 'Table'[Year] = EARLIER ( 'Table'[Year] )
            && 'Table'[Month] = EARLIER ( 'Table'[Month] )
    )
)

 

vzhangti_1-1636681387511.png

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

smpa01
Super User
Super User

@actroyani  you can try this

 

Column =
VAR _0 =
    ADDCOLUMNS (
        ADDCOLUMNS ( tbl, "year", YEAR ( tbl[Date] ) ),
        "month", MONTH ( tbl[Date] )
    )
VAR _1 =
    ADDCOLUMNS (
        _0,
        "test",
            SUMX (
                FILTER (
                    _0,
                    [Person] = EARLIER ( [Person] )
                        && [year] = EARLIER ( [year] )
                        && [month] = EARLIER ( [month] )
                ),
                [Sales]
            )
    )
RETURN
    MAXX (
        FILTER ( _1, [Person] = EARLIER ( tbl[Person] ) && [Date] = EARLIER ( [Date] ) ),
        [test]
    )

 

 

smpa01_0-1636481961129.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.