Forum Discussion

TedLindkvist's avatar
TedLindkvist
Frequent Visitor
1 year ago
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

Since I'm working with direct query, a new column isnt the most optimal, so a measure is preferred.

My task is to show the main article (i.e. the one with most sales value) for each order.

So for the example below, I would like the line of Order Number 1, to give me the value "Car" for the enire order value (33,000), since that is the largest ordervalue in that specific order number.

Tables looks something like;
Table 1

Article CodeArticle name
111Interior
112Tyre
113Car


Table 2 

Order NumberArticle CodeArticle value
11111,000
11122,000
111330,000
21122,000
221120,000
22125,000
Need Help
  • Kedar_Pande's avatar
    Kedar_Pande
    1 year ago

    TedLindkvist 

    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

3 Replies

  • Kedar_Pande's avatar
    Kedar_Pande
    Icon for Super User rankSuper User
    1 year ago

    TedLindkvist 

    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

  • 123abc's avatar
    123abc
    Icon for Community Champion rankCommunity Champion
    1 year ago

     

    First, create a measure that calculates the maximum article value for each order:

    Below measure calculates the maximum article value for each Order Number by ignoring any filters except those on Order Number.

     

    MaxOrderValuePerOrder = CALCULATE( MAX('Table 2'[Article value]), ALLEXCEPT('Table 2', 'Table 2'[Order Number]) )

     

    Now, create a measure to retrieve the article name based on the highest order value:

     
    MainArticle = CALCULATE( FIRSTNONBLANK('Table 1'[Article name], 1), FILTER( 'Table 2', 'Table 2'[Article value] = [MaxOrderValuePerOrder] && 'Table 2'[Order Number] = SELECTEDVALUE('Table 2'[Order Number]) ), 'Table 1'[Article Code] = 'Table 2'[Article Code] )
     
    • FILTER filters Table 2 to find rows where Article value matches the maximum value for that order.
    • FIRSTNONBLANK retrieves the first Article name for the matching rows.
    • The relationship between Table 1 and Table 2 on Article Code allows the Article name to be pulled correctly.