Forum Discussion
Solved
Main article in order based on order value from different table
Hi, Tried to search my way but didnt really find a good match yet. I have 2 tables, One consisting of order with article codes and sales value The other with article code and article names ...
Create a relationship between the two tables on the Article Code column.
Create a measure to find the main article for each order.
Main Article =
VAR MaxArticleValue =
CALCULATE(
MAX('Table 2'[Article value]),
ALLEXCEPT('Table 2', 'Table 2'[Order Number])
)
VAR MainArticleCode =
CALCULATE(
MAX('Table 2'[Article Code]),
'Table 2'[Article value] = MaxArticleValue,
ALLEXCEPT('Table 2', 'Table 2'[Order Number])
)
RETURN
CALCULATE(
MAX('Table 1'[Article name]),
'Table 1'[Article Code] = MainArticleCode
)💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn
Kedar_Pande
Super User
Super UserCreate a relationship between the two tables on the Article Code column.
Create a measure to find the main article for each order.
Main Article =
VAR MaxArticleValue =
CALCULATE(
MAX('Table 2'[Article value]),
ALLEXCEPT('Table 2', 'Table 2'[Order Number])
)
VAR MainArticleCode =
CALCULATE(
MAX('Table 2'[Article Code]),
'Table 2'[Article value] = MaxArticleValue,
ALLEXCEPT('Table 2', 'Table 2'[Order Number])
)
RETURN
CALCULATE(
MAX('Table 1'[Article name]),
'Table 1'[Article Code] = MainArticleCode
)
💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn
Worked like a charm, cheers!
