Sum up Amounts grouped by Currency and concatenate Currency in the measure

[modi123p]

Hi,

I need a Measure that calculates summation of amounts based on currency (and other columns in the table). the output shall be separated by Currency.

Input Table:

Date	ColA	Currency	Amount
01-01-20	A	EUR	1000
01-01-20	A	USD	2000
01-01-20	A	EUR	3000
01-01-20	B	USD	1000
02-01-20	A	EUR	2000
02-01-20	A	USD	3000
02-01-20	A	EUR	1000
02-01-20	A	USD	2000
02-01-20	A	EUR	3000
02-01-20	B	USD	1000
02-01-20	B	EUR	2000
02-01-20	B	USD	3000

Expected Output:

ColA	Measure
A	10000 EUR + 7000 USD
B	2000 EUR + 5000 USD

I tried with the below expression but the values are not showing correctly:

Amount = CALCULATE(SUM('Table'[Amount]), ALL('Table'[Currency]))

Total By Currency = CONCATENATEX(VALUES('Table'[Currency]), FORMAT([Amount], "###,###,##0") & " " & SELECTEDVALUE('Table'[Currency]), " + ", [Currency], ASC)

Need your immediate assistance.

Thanks in advance.

Regards,

Mannu

[CNENFRNL · Accepted solution]

Hi, @modi123p , you may want to try this measure

Total By Currency = 
CONCATENATEX (
    VALUES ( 'Table'[Currency] ),
    FORMAT ( CALCULATE ( SUM ( 'Table'[Amount] ) ), "#,##0" ) & " " & 'Table'[Currency],
    " + ",
    'Table'[Currency], ASC
)