Forum Discussion

JoyceW's avatar
JoyceW
Helper II
3 years ago
Solved

Group by margin %

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...
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi JoyceW 

    you would need

    1) a table named Range like this:

    RangeMinMax
    <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:

     

FreemanZ's avatar
FreemanZ
Super User
3 years ago

hi JoyceW 

you would need

1) a table named Range like this:

RangeMinMax
<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's avatar
JoyceW
Helper II
3 years ago

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

    )

)