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 ha...
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.