Group by margin %

[JoyceW]

I have a table with sales order lines from the last 3 years. I have revenue and costs and measures to calculate the margin and margin percentage.

now I would like to create a visual that shows how many orders and revenue have a margin below 8 procent. How many with margin between 8 and 10 procent and how many above 10 percent.

Any advice would be appreciated, thank you!

[JoyceW]

Sorry, here an example to explain clearer. 

data:

Ordernumber	Orderdate	Revenue	Costs	Margin	Margin%
1	1-1-2022	10	8	2	20%
2	1-2-2022	12	9	3	25%
3	1-3-2022	14	13	1	7%
4	1-4-2022	16	14	2	13%
5	1-5-2022	18	17	1	6%
6	1-6-2022	20	10	10	50%
7	1-7-2022	22	15	7	32%
8	1-8-2022	24	20	4	17%
9	1-9-2022	26	25,7	0,3	1%
10	1-10-2022	28	26	2	7%
11	1-11-2022	26	29	-3	-12%
12	1-12-2022	24	32	-8	-33%
13	1-1-2023	22	35	-13	-59%
14	1-2-2023	20	19	1	5%
15	1-3-2023	18	17,5	0,5	3%
16	1-4-2023	16	15	1	6%
17	1-5-2023	14	12	2	14%
18	1-6-2023	12	11	1	8%
19	1-7-2023	10	9	1	10%
20	1-8-2023	8	7	1	13%

And that should result in something like this:

Year
2022			2023
	Revenue	Count of orders	Revenue	Count of orders		difference
<8%	136	6	76	4		-60
8-10%	0	0	22	2		22
>10%	104	6	22	2		-82

[FreemanZ · Accepted solution]

hi @JoyceW 

you would need

1) a table named Range like this:

Range	Min	Max
<8%	-100.00%	7.99%
8-10%	8.00%	10.00%
>10%	10.01%	100.00%

2) add a calculated column in your table:

Year = YEAR(TableName[Orderdate])

3) plot a matrix visual with necessary columns and three measures like:

Revenue2 = 
SUMX(
    FILTER(
        TableName,
        TableName[Margin%]<=MAX(Range[MAX])
            &&TableName[Margin%]>=MAX(Range[MIN])
    ),
    TableName[Revenue]
)

OrderCount = 
COUNTROWS(
    FILTER(
        TableName,
        TableName[Margin%]<=MAX(Range[MAX])
            &&TableName[Margin%]>=MAX(Range[MIN])
    )
)

RevenueDiff = 
VAR _year  = MAX(TableName[Year])
RETURN
CALCULATE(
    [Revenue2],
    TableName[Year] = _year
)
-
CALCULATE(
    [Revenue2],
    TableName[Year] = _year -1
)

it worked like:

[JoyceW]

Hi! In my real report the countorders result is not correct. When the 3 amounts are added up the sum is higher than the amount of orders. 

This is the DAX I'm using:

Now I have orders that are shipped in multiple days. And some of the actual margins are for example
-239900,00%.

Could either be the reason that it is not working properly?

AantalOrdersPerGroep = 

COUNTROWS(

        FILTER(

        'Verkoop per dag',

        [Marge %]<=MAX(MargeGroep[Max])

        &&[Marge %]>=MAX(MargeGroep[Min])

    )

)

[qrasooli]

You can create a new column and use an if statement and say if the margin is >0 and <=8% , result "Margin Below 8%". And go from there and add conditions and categories you would like e.g 8%-20%, 20%-30%, and so on and so forth, and from there sort your table by this column. Hope this helps. Let me know what you come up with.

[FreemanZ]

hi @JoyceW 

better to provide sample data with expected result, e.g. what time frame for your measures?