summarize groupby or calculate?

[Tilo26]

Hi all,

I'm looking for a solution for the following Situation.

My Table looks like:

Month, Sales Person, count of Orders, Profit, Revenue.

What is the best way to find out the monthly Profit, Revenue and count of Orders by each Sales Person?

Example data is attached.

Hope someone can help. Thanks!

 

Best,

Tilo

[technolog]

I hope this message finds you well. I've noticed that this solution remain unresolved. If any of you have managed to find a resolution to the issue, I kindly request that you share your solution for the benefit of the entire community. Alternatively, if you're still facing challenges, please do let us know as well.

Your insights and updates will greatly assist others who might be encountering the same challenge.

[v-shex-msft]

HI @Tilo26,

I'd like to suggest you to extract the current date as condition to filter and summary records.

Sample measure formulas:

monthly Profit =
VAR currDate =
    MAX ( Table1[Order Date] )
RETURN
    CALCULATE (
        SUM ( Table1[Profit] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            YEAR ( [Order Date] ) = YEAR ( currDate )
                && MONTH ( [Order Date] ) = MONTH ( currDate )
        ),
        VALUES ( Table1[Sales Person] )
    )

monthly Revenue =
VAR currDate =
    MAX ( Table1[Order Date] )
RETURN
    CALCULATE (
        SUM ( Table1[Revenue] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            YEAR ( [Order Date] ) = YEAR ( currDate )
                && MONTH ( [Order Date] ) = MONTH ( currDate )
        ),
        VALUES ( Table1[Sales Person] )
    )
    
Order count =
VAR currDate =
    MAX ( Table1[Order Date] )
RETURN
    CALCULATE (
        SUM ( Table1[Amount of Order] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            YEAR ( [Order Date] ) = YEAR ( currDate )
                && MONTH ( [Order Date] ) = MONTH ( currDate )
        ),
        VALUES ( Table1[Sales Person] )
    )

If you mean to create a new summary table that group records based on date and person, you can also try to use following calculate table formula:

summary table =
SUMMARIZE (
    ADDCOLUMNS (
        ALL ( Table1 ),
        "Year", YEAR ( [Order Date] ),
        "Month", MONTH ( [Order Date] )
    ),
    [Sales Person],
    [Year],
    [Month],
    "Monthly Profit", SUM ( Table1[Profit] ),
    "monthly Revenue", SUM ( Table1[Revenue] ),
    "Order count", SUM ( Table1[Amount of order] )
)

Regards,

Xiaoxin Sheng

[eliasayyy]

hmm well if you want by month then it depends what you are trying to do if you want measures , 

then simply use calculate with expressions like sum and count but if you want  anew table that summarizes all of those just use summarize im happy to help if you get stuck somewhere 

[Tilo26]

Can you provide me with the Code?

[eliasayyy]

glad ly but you need to share sample data and what you want to help you with the code

[Tilo26]