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

[Anonymous]

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

[v-lionel-msft]

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]

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]

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!

[Anonymous]

Hi @Tahreem24 

That will be the maximum value on my card visual not the category name which has the maximum value

I need the category name in my card visual. In this example, My card visual should show Category A because it has value 40 which is greater than Category B and Category C.

[Tahreem24 · Accepted solution]

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!

[Anonymous]

Bravo!!!!

[Tahreem24]

@Anonymous , Sorry for misundertood your problem earlier. Later I realized your proper statement.

Good Luck!!!