Forum Discussion
Group by margin %
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:
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 |
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:
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])
)
)