Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX formulae to get maximum value based on column from different table

Hi All,
What I need is Sum of values of each category where category is one table and value is in another. Here is the ex:
Table 1:
ID      Category
1             A
2             B
3             C

 

Table 2:
ID           Value
1               10

1               20

1               10

2               10

3                 5

 

So as above: Category A has total value 40 and Category B has 10 and Category C has 5
I need to show in a card the Category Name with Highest Value. How can I do this?

Thanks,

Ankku

 

 

 

Need Help
Show and Tell
Tips and Tricks
  • Tahreem24's avatar
    Tahreem24
    6 years ago

    Hi Anonymous ,

     

    Please try this measure:

    Top Category = MAXX(TOPN(1,VALUES(Table1[Category]),CALCULATE(Sum(Table2[Value])),DESC),Table1[Category])
     
    And see the expected output in below screen shot:
     
    Please give KUDOS for support  and also accept this as a SOLUTION if it helps you!
     

7 Replies

  • v-lionel-msft's avatar
    v-lionel-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    The relationship between tables:

     

    Create a measure:

    // Measure
maximum value = 
 CALCULATE(
     SUM('Sheet3 (2)'[ Value]),
     ALLEXCEPT(
         'Sheet3 (2)',
         'Sheet3 (2)'[ID          ], Sheet4[Category]
     )
)

     

    Add a card visual:

     

    Is tihs what you want?

     

    Best regards,
    Lionel Chen

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Lionel Chen,

      v-lionel-msft 

       

      Thanks for response but this is not what I want. I am able to get the maximum value for each category but now in the card visual I should show only that category which is maximum of three. If I add this measure in Card visual it will give me the sum of values while what I am looking for is to display the category which will be have maximum value.

       

      Thanks, Ankku

      • Tahreem24's avatar
        Tahreem24
        Icon for Super User rankSuper User
        6 years ago

        Hi Anonymous ,

         

        After creating a relationship between those two tables as mentioned by v-lionel-msft . You can just create one measure like below:

        Measure = Max(Table[Values])

        And take Card visual (Single Card) from visualization pane and drag this measure.

         

        Give me some time i will try to replica your issue at my side and will post output in some time.

        Please give KUDOS for support and accept this as a solution if it helps you!