Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
UserSepp
Frequent Visitor

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

Database.png

 

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 3
wdx223_Daniel
Super User
Super User

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

v-xiandat-msft
Community Support
Community Support

Hi @UserSepp ,

Below is my table:

vxiandatmsft_0-1704161467800.png

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:

vxiandatmsft_1-1704161532669.png

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.

123abc
Community Champion
Community Champion

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.

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors