Search code examples
powerbidaxpowerbi-desktop

(DAX) How to sum sales for orders with multiple order lines based on Dimension set ID. Not all order lines in the table has the dimension set id

First question on the platform and a newbie at Powerbi.

I have a table with multiple order lines. I need to calculate the sum for each order that has a specific dimension set ID, ex. "123".

The issue consists of the fact that not all order lines in the table has the dimension set id. Therefore when i make a calculated sum I only get the SUM of the order rows with the dimension set id and not the entire order value which i need.

My question is; how do I make a new column that sets the Dimension set ID for all order lines if Dimension set ID is true in ANY of the orderlines for the same order?

example of table

I tried using the IF function to find all order numbers but with no luck since the row only references itself and therefore, if the dimension set ID is blank, no value is given:

IF('Table'[Dimension Set ID]>0,'Table'[Order_no],"")

Solution

  • Best option is to do this in Power Query with the Fill down option. See Fill values in a column.

    Otherwise, the DAX Calculated Column could look like:

    IF(
  'Table'[Dimension Set ID] > 0,
  'Table'[Dimension Set ID],
  CALCULATE(
    MAX('Table'[Dimension Set ID]),
    ALLEXCEPT('Table', 'Table'[Order_no])
   )
)