Concatenate in a Pivot Table

[gmazza76]

Good afternoon,

I currently do calculations in a long winded formula that pulls the information from a table and shows the information as "100% (2)"

But if I do this in a pivot table I can complete the score 'Pass/'Completed' to gibe the % but can I add the (2) in the same output field so the table isn't to large as it shows as 5 individual columns where as if I need to do them all separately that would end up as 10 columns.

=CONCATENATE(TEXT(SUM(SUMIFS('Sheet1'!$M:$M,'QA Data'!$B:$B,Main!$F$2,'QA Data'!$D:$D,Main!$F$1,'QA Data'!$F:$F,C165)/SUMIFS('Sheet1'!$L:$L,'Sheet1'!$B:$B,Main!$F$2,'QA Data'!$D:$D,Main!$F$1,'Sheet1'!$F:$F,C165)),"0%")," (",SUMIFS('Sheet1'!$L:$L,'QA Data'!$B:$B,Main!$F$2,'QA Data'!$D:$D,Main!$F$1,'Sheet1'!$F:$F,C165),")"))

thanks
Gavin

 

[RoryA]

Yes, if you use the data model and power pivot. You can then use CONCATENATEX to return text in the values area.

 

[gmazza76]

thanks @RoryA , do you have any useful links I could look at for this?

 

[RoryA]

The syntax for concatenatex can be found here: CONCATENATEX – DAX Guide

Your expression will need to concatenate the two measures for each row in the format you want, and you'll probably want to either turn off grand totals or have the measure return something different at that level.

 

[alansidman]

Here is a step by step tutorial
ttps://www.sfmagazine.com/articles/2018/july/excel-reporting-text-in-a-pivot-table/