Group orderlines by order no. and filter

[Anonymous]

Hi all 🙂

I have a sales table that looks like this:

Orderline	Order Number	Sales Profit	Discount	Adjusted	YearMonth
1	1	5	10	Manually	2020-05
2	1	2	5		2020-05
3	2	10	15	Manually	2020-05
4	2	3	8	Manually	2020-06
5	3	20	10	Manually	2020-05

I would like to group the order numbers and count the profit discount % per distinct order number, but only include orderlines with "Manually" in the adjusted column.

The profit discount % = Discount / (Sales Profit + Discount) 

Essentially i need the table to look like this below or somehow make a measure that can count order colors by month given a filter on "adjusted" and categorize orders by profit discount. (just whatever is possible or easiest).

Order Number	Sales Profit	Discount	Adjusted	YearMonth	Profit discount_%	order_color
1	5	10	Manually	2020-05	66,6%	Red
2	13	23	Manually	2020-05	63,8%	Red
3	20	10	Manually	2020-05	33,3%	Orange

Then I would like to show count of distinct order numbers per month categorized into 3 groups based on the Profit Discount  % and if orderlines within same order number is in 2 months, just take the earliest month.

Between 0-25% profit discount = BLUE

Between 25-50% profit discount = ORANGE

Above 50% profit discount = RED

In this sample case the graph would show 2 red and 1 orange in 2020-05

Hope it makes sense. Would really appreciate your help. thanks!

[v-eqin-msft · Accepted solution]

Hi @Anonymous ,

I did it in two ways.

1. Create measures:

Discount Measure =
CALCULATE (
    SUM ( 'Sales'[Discount] ),
    FILTER (
        'Sales',
        'Sales'[Order Number ] = MAX ( 'Sales'[Order Number ] )
            && 'Sales'[Adjusted] = "Manually"
    )
)


Profit =
CALCULATE (
    SUM ( 'Sales'[Sales Profit] ),
    FILTER (
        'Sales',
        'Sales'[Order Number ] = MAX ( 'Sales'[Order Number ] )
            && 'Sales'[Adjusted] = "Manually"
    )
)


Profit discount_% =
[Discount Measure] / ( [Discount Measure] + [Profit] )


Year-Month =
CALCULATE (
    MIN ( 'Sales'[YearMonth] ),
    FILTER (
        'Sales',
        'Sales'[Order Number ] = MAX ( 'Sales'[Order Number ] )
            && 'Sales'[Adjusted] = "Manually"
    )
)


order_color =
IF (
    [Profit discount_%] >= 0
        && [Profit discount_%] <= 0.25,
    "Blue",
    IF ( [Profit discount_%] > 0.25 && [Profit discount_%] <= 0.5, "Orange", "Red" )
)

2. Create a new calculated table:

Table =
VAR _t =
    DISTINCT ( 'Sales'[Order Number ] )
RETURN
    ADDCOLUMNS (
        _t,
        "Sales",
            VAR _disOrder = 'Sales'[Order Number ]
            RETURN
                SUMX (
                    FILTER (
                        ALL ( 'Sales' ),
                        [Adjusted] = "Manually"
                            && [Order Number ] = _disOrder
                    ),
                    [Sales Profit]
                ),
        "Profit Discount",
            VAR _disOrder = 'Sales'[Order Number ]
            RETURN
                SUMX (
                    FILTER (
                        ALL ( 'Sales' ),
                        [Adjusted] = "Manually"
                            && [Order Number ] = _disOrder
                    ),
                    [Discount]
                ),
        "Adjusted", "Manually",
        "YearMonth",
            VAR _disOrder = 'Sales'[Order Number ]
            RETURN
                MINX (
                    FILTER (
                        ALL ( 'Sales' ),
                        [Adjusted] = "Manually"
                            && [Order Number ] = _disOrder
                    ),
                    [YearMonth]
                )
)

Then add a column to it:

Profit discount_% =
[Profit Discount] / ( [Profit Discount] + [Sales] )

And creata a measure for conditional formatting:

Order color =
IF (
    MAX ( 'Table'[Profit discount_%] ) >= 0
        && MAX ( 'Table'[Profit discount_%] ) <= 0.25,
    "Blue",
    IF (
        MAX ( 'Table'[Profit discount_%] ) > 0.25
            && MAX ( 'Table'[Profit discount_%] ) <= 0.5,
        "Orange",
        "Red"
    )
)

The final output looks like this:

Here is the pbix file.

Did I answer your question ? Please mark my reply as solution. Thank you very much.

If not, please upload some insensitive data samples and expected output.

Best Regards,

Eyelyn Qin

[littlemojopuppy]

Here's some code...

Order Count:=CALCULATE(
		COUNTROWS(Orders),
		FILTER(
			Orders,
			[Adjusted] = "Manually"
		)
	)


Total Sales Profit:=CALCULATE(
		SUM(Orders[Sales Profit]),
		FILTER(
			Orders,
			[Adjusted] = "Manually"
		)
	)


Total Discount:=CALCULATE(
		SUM(Orders[Discount]),
		FILTER(
			Orders,
			[Adjusted] = "Manually"
		)
	)


Profit Discount %:=DIVIDE(
		[Total Discount],
		[Total Sales Profit] + [Total Discount],
		BLANK()
	)


Order Color:=SWITCH(
	TRUE(),
	AND(
		[Profit Discount %] >=  0,
		[Profit Discount %] < .25
	), "BLUE",
	AND(
		[Profit Discount %] >= .25,
		[Profit Discount %] < .50
	), "ORANGE",
	"RED"
)