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 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!

 

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

     

5 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    hi JoyceW 

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

  • qrasooli's avatar
    qrasooli
    Frequent Visitor
    3 years ago

    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.

  • JoyceW's avatar
    JoyceW
    Helper II
    3 years ago

    Sorry, here an example to explain clearer. 

    data:

     

    OrdernumberOrderdateRevenueCostsMarginMargin%
    11-1-2022108220%
    21-2-2022129325%
    31-3-2022141317%
    41-4-20221614213%
    51-5-2022181716%
    61-6-202220101050%
    71-7-20222215732%
    81-8-20222420417%
    91-9-20222625,70,31%
    101-10-2022282627%
    111-11-20222629-3-12%
    121-12-20222432-8-33%
    131-1-20232235-13-59%
    141-2-2023201915%
    151-3-20231817,50,53%
    161-4-2023161516%
    171-5-20231412214%
    181-6-2023121118%
    191-7-2023109110%
    201-8-202387113%

     

    And that should result in something like this:

     

    Year      
    2022  2023   
     RevenueCount of ordersRevenueCount of orders difference
    <8%1366764 -60
    8-10%00222 22
    >10%1046222 -82

     

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

            )

        )