Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.