SUM by categories of Max by categories

[vnqt]

Hi,

I have 2 related tables : 

 Table 1 

Group	Nom
BCR	Nom 1
BCR	Nom 2
BCR	Nom 3
CA	Nom 4
CA	Nom 5
CA	Nom 6
CLS	Nom 7
CLS	Nom 8
CLS	Nom 9
COM	Nom 10
COM	Nom 11
TES	Nom 12
TES	Nom 13

Table 2 

Nom	Used	KEY
Nom 1	21	KEY A
Nom 2	10	KEY A
Nom 3	11	KEY A
Nom 4	12	KEY A
Nom 5	13	KEY A
Nom 6	14	KEY A
Nom 7	15	KEY B
Nom 8	16	KEY B
Nom 9	17	KEY B
Nom 10	18	KEY C
Nom 11	52	KEY C
Nom 12	36	KEY C
Nom 13	94	KEY C

I would like to create the following matrix 

KEY	GROUP	USED MAX	Total KEY
A			35 (21+14)
	BCR	21
	CA	14
B			17
	CLS	17
C			146 (52+94)
	COM	52
	TES	94

Thank you in advance

[powerbidev123 · Accepted solution]

Hi @vnqt , I believe only a measure for the total should be created. 

Total= Calculate(sum(table2[Key]),ALLEXCEPT(table2,table2[Key]))

[vojtechsima · Accepted solution]

Hello, @vnqt ,

 try this:

max = 
var _max = MAX(table2[Used])
var _maxTotal = SUMX( VALUES('Table'[Group]), CALCULATE( MAX( table2[Used])))
var _result = IF( NOT ISINSCOPE('Table'[Group]), _maxTotal, _max)

return _result

[v-kongfanf-msft · Accepted solution]

Hi @vnqt ,

Agree with @vojtechsima solution, you can also try the following formula, which is also a good choice:

MaxUsedByGroup_ = 
VAR _max = MAX(Table2[Used])
VAR _maxTotal = SUMMARIZE(
    Table1,
    Table1[Group],
    "MaxUsed", CALCULATE(MAX(Table2[Used]))
)
VAR _result = IF(
    NOT ISINSCOPE(Table1[Group]),
    SUMX(_maxTotal, [MaxUsed]),
    _max
)
RETURN _result

Best Regards,
Adamk Kong

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

[vnqt]

Many thanks for your helps. I work all the solutions. 

[v-kongfanf-msft]

Hi @vnqt ,

Thanks for your feedback.😀

Best regards,

Adamk Kong

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[v-kongfanf-msft · Accepted solution]

Hi @vnqt ,

Agree with @vojtechsima solution, you can also try the following formula, which is also a good choice:

MaxUsedByGroup_ = 
VAR _max = MAX(Table2[Used])
VAR _maxTotal = SUMMARIZE(
    Table1,
    Table1[Group],
    "MaxUsed", CALCULATE(MAX(Table2[Used]))
)
VAR _result = IF(
    NOT ISINSCOPE(Table1[Group]),
    SUMX(_maxTotal, [MaxUsed]),
    _max
)
RETURN _result

Best Regards,
Adamk Kong

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

[vojtechsima · Accepted solution]

Hello, @vnqt ,

 try this:

max = 
var _max = MAX(table2[Used])
var _maxTotal = SUMX( VALUES('Table'[Group]), CALCULATE( MAX( table2[Used])))
var _result = IF( NOT ISINSCOPE('Table'[Group]), _maxTotal, _max)

return _result

[powerbidev123 · Accepted solution]

Hi @vnqt , I believe only a measure for the total should be created. 

Total= Calculate(sum(table2[Key]),ALLEXCEPT(table2,table2[Key]))