Data from table in text measure

[KLJ]

Hi

I have a measure with the following text:

"The value of goods on sea is " & SUM([goodsonsea])

And further down

"The value of goods in purchase orders is " & SUM([purchaseorders])

In between the two lines I would like to list the data from this table:

Supplier	Week	Cll
Supplier1	42	6
Supplier2	42	5
Supplier1	44	5
Supplier2	50	3
Supplier3	42	4
Supplier4	44	4

The text could be something like
"In week 42 we receive 6 cll from Supplier1 and 5 cll from supllier2 ect."

"In week 44 we receive 5 cll from Supplier1 and 4 cll from Supplier4 ect"

and so on.

I hope this give you an idea of what it is I'm looking for.

Hope you can help 🙂

Thank you.

[v-qiuyu-msft · Accepted solution]

Hi @KLJ,

In Power BI desktop, you can create a calculated table below: 

Table = var temp= SUMMARIZE('Table1',Table1[Week],'Table1'[Supplier],"con",CONCATENATEX('Table1','Table1'[Cll],","))
return
ADDCOLUMNS(temp,"item",[con]&" cll from "& [Supplier])

Then create a measure: 

Measure 3 = var t=SUMMARIZE('Table','Table'[Week],"Detail",CONCATENATEX('Table','Table'[item]," and "))
return
CONCATENATEX(t,"In week " & [Week]& " we receive "&[Detail])

Best Regards,
Qiuyun Yu 

[KLJ]

Hi Qiuyun Yu

Thank you for the solution.

That did it 🙂

I have added UNICAR(10) at the end of 

Measure 3 = var t=SUMMARIZE('Table','Table'[Week],"Detail",CONCATENATEX('Table','Table'[item]," and "))
return 
CONCATENATEX(t,"In week " & [Week]& " we receive "&[Detail] & UNICHAR(10))

then it worked out exactly as I wanted. 🙂

Thanks again

Kim