Forum Discussion

KLJ's avatar
KLJ
Icon for Helper I rankHelper I
8 years ago
Solved

Data from table in text measure

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:

 

SupplierWeekCll
Supplier1 426
Supplier2425
Supplier1 445
Supplier2503
Supplier3424
Supplier4444

 

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.

 

 

Need Help
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    8 years ago

    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 

2 Replies

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    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's avatar
      KLJ
      Icon for Helper I rankHelper I
      8 years ago

      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