Forum Discussion

UserSepp's avatar
UserSepp
Frequent Visitor
2 years ago

Ignore specific sales orders

Hello and thank you for your time! 🙏

I need to calculate the average of sales per customer for the points of sale (formula: total amount / number of orders).

 

The challenge:
Orders that only have one order item wich is the article 13004 should no longer be included in the calculation.

So I need to know
1) Which order number has only one order item
2) If the order item is art_nr 13004
3) I have to subtract the number of these orders and the amount of sales from this orders from the total to be able to recalculate the sales per customer 

 

The table with the data looks like this

 

I don't know how to put this into a DAX formula. I hope you can help me with this.

 

Thank you for your time and your help 🙏

3 Replies

  • 123abc's avatar
    123abc
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Certainly! The task involves working with a DAX formula in Power BI (or a similar tool that uses DAX). Here's how you can approach this:

    1. Identify Orders with Only One Order Item and Article 13004: First, you'll need to identify which orders have only one order item and if that order item is art_nr 13004.

    2. Calculate Sales per Customer: After identifying those orders, you'll subtract the sales and the count of such orders from the total to recalculate the sales per customer.

    Here's a step-by-step DAX formula to achieve this:

     

    Sales Per Customer Adjusted =
    VAR TotalSales = SUM('YourTableName'[total_amount])
    VAR TotalOrders = COUNTROWS('YourTableName')

    VAR SingleItemOrdersWith13004 =
    CALCULATE(
    COUNTROWS('YourTableName'),
    FILTER(
    ALL('YourTableName'),
    CALCULATE(
    COUNTROWS('YourTableName'),
    ALLEXCEPT('YourTableName', 'YourTableName'[order_number])
    ) = 1 &&
    SUMMARIZE(
    FILTER('YourTableName', 'YourTableName'[art_nr] = 13004),
    'YourTableName'[order_number]
    )
    )
    )

    VAR AdjustedTotalSales =
    TotalSales -
    CALCULATE(
    SUM('YourTableName'[total_amount]),
    FILTER(
    ALL('YourTableName'),
    CALCULATE(
    COUNTROWS('YourTableName'),
    ALLEXCEPT('YourTableName', 'YourTableName'[order_number])
    ) = 1 &&
    SUMMARIZE(
    FILTER('YourTableName', 'YourTableName'[art_nr] = 13004),
    'YourTableName'[order_number]
    )
    )
    )

    VAR AdjustedTotalOrders = TotalOrders - SingleItemOrdersWith13004

    RETURN
    AdjustedTotalSales / AdjustedTotalOrders

     

    Replace 'YourTableName' with the actual name of your table.

    Here's a breakdown of what's happening:

    • SingleItemOrdersWith13004 calculates the count of orders that have only one item, and that item is art_nr 13004.
    • AdjustedTotalSales subtracts the total sales from orders with just one item of art_nr 13004.
    • AdjustedTotalOrders adjusts the total orders by subtracting the count of single-item orders with art_nr 13004.
    • Finally, the formula returns the adjusted sales per customer.

    Make sure to adjust table and column names according to your actual dataset.

     
     
     

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

     

    In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi UserSepp ,

    Below is my table:

    The following DAX might work for you:

     

    total_number = 
    'Table'[quantity]*'Table'[sales_amount]

Measure = 
     CALCULATE(
        SUM('Table'[total_number])/SUM('Table'[quantity]),
        FILTER('Table','Table'[art_nr]<>13004)
     )

     

    The final output is shown in the following figure:

    Best Regards,

    Xianda Tang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    2 years ago

    NewMeasure=SUMX(VALUES(Table[order_nr]),IF(COUNTROWS(EXCEPT(CALCULATETABLE(VALUES(Table[art_nr])),{13004}))>0,Table[Sales_Amount]))