Forum Discussion

backflash's avatar
backflash
Icon for Helper II rankHelper II
4 years ago
Solved

Use AVERAGEX with two arguments for KEEPFILTERS

Hey All,

 

I have a problem that should be very simple to solve - and I know two possible workarounds (see below) - but maybe there is a better way with the correct usage of the DAX-power 😉

 

My situation:

I have a table that contains all the order_details (positions) of all orders with columns like 'order-nr', 'product-nr', etc.

I was creating some AVERAGEX measures for my report and it works fine, as long as I have only one condition/argument for the KEEPFILTERS part.

 

Example:

Different products in all orders on average =
AVERAGEX(
    KEEPFILTERS(VALUES('order_details'[order-Nr])),
    CALCULATE(DISTINCTCOUNT('order_details'[productname]))
)
then I filter more filters like country etc. to check if the amount of diff. products in an order changes within a country...
 
Same for a measure that gives me how many items of one product has been ordered in average with:
 
AVERAGEX(
    KEEPFILTERS(VALUES('order_details'[productname])),
    CALCULATE(SUM('order_details'[order_amount]))
)
 
 
 
But I have a special situation, that in my order_details table, I can see a product with a unique product_name or Product_number, which can have different colors.
When different colors of one product are ordered, there are in different order positions, but the product name / number is still the same (I have not a unique number for the product-color-combination, but have to use both columns to distinguish)
 
I now want to find out, how many items of a product-color-combination have been ordered on average.
 
I was thinking of something like
 
AVERAGEX(
    KEEPFILTERS(VALUES('order_details'[productname]) AND 'order_details'[product_color]),
    CALCULATE(SUM('order_details'[order_amount]))
)
 
but I cannot combine two arguments in the KEEPFILTERS area.
When I only use color, I get all the red products, but not the red version or product XY.
 
My ideas as workarounds:

Do I have to 'outsource' the KEEPFILTER part with different Variables and use the older 'FILTER...ALL' possibilities of DAX?

Or would it be better to optimize my data and include a new column with is combining 'product-name' and 'color' to get a 'product-version' kind of column?

Or is there even a better way? 🙂
Looking forward to some best practices in that case.
 
Thanks in advance!
 
Regards from Germany
Vanessa
 
Need Help
  • parry2k's avatar
    parry2k
    4 years ago

    backflash you can use summarize

     

    AVERAGEX(
SUMMARIZE ( 'Order_details','order_details'[productname],'order_details'[product_color]),
    CALCULATE(SUM('order_details'[order_amount]))
)

     

    ✨ Follow us on LinkedIn and  to our YouTube channel

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

3 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    4 years ago

    backflash you can use summarize

     

    AVERAGEX(
SUMMARIZE ( 'Order_details','order_details'[productname],'order_details'[product_color]),
    CALCULATE(SUM('order_details'[order_amount]))
)

     

    ✨ Follow us on LinkedIn and  to our YouTube channel

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

  • backflash's avatar
    backflash
    Icon for Helper II rankHelper II
    4 years ago

    parry2kgreat - that is just what I need! Thanks.
    Now I can better understand when SUMMARIZE could come in handy 🙂