topic Re: Group by margin % in DAX Commands and Tips

Group by margin %

JoyceW — Thu, 06 Apr 2023 20:24:33 GMT

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!

Re: Group by margin %

FreemanZ — Thu, 06 Apr 2023 22:52:33 GMT

hi JoyceW

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

Re: Group by margin %

qrasooli — Thu, 06 Apr 2023 23:01:41 GMT

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.

Re: Group by margin %

JoyceW — Fri, 07 Apr 2023 06:28:56 GMT

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

Re: Group by margin %

FreemanZ — Fri, 07 Apr 2023 08:19:07 GMT

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:

Re: Group by margin %

JoyceW — Mon, 10 Apr 2023 17:33:49 GMT

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])

    )

)