Showing the a column as text in Power Pivot Table next to a summarized variable (Power Pivot)

Question

I have an Excel table like this:

AZIENDA  |  CF  |  Fatturato  | Semester
A        |  333d|  10         | 1 
A        |  333d|  20         | 2 
B        |  145 |  34         | 1
B        |  145 |  10         | 2  
C        |  111 |  5          | 1

Using Power Pivot, I want this outcome as a Pivot Table:

AZIENDA  | Fatturato   | CF
A        | 30          | 333d 
B        | 44          | 145
C        | 5           | 111

Main problem is the creation of the column CF. I tried to create this measure

=CONCATENATEX(Tabella1,Tabella1[CF],",")

But it does not work. Any better idea?

If `CF` is always duplictes, just change that field to show the `MAX()` or `MIN()`? — JvdV, Dec 14 '20 at 15:20
"We can't summarize this field with Max because it's not a supported calculation for text data types". There are some letters somewhere. Better update the question, sorry — coelidonum, Dec 14 '20 at 15:25
Well, if you load your data into powerquery you can simply group by colums AZIENDA and CF. Some shuffeling of columns will get you exactly what you are after. I don't think you can get text values into Pivot Table. — JvdV, Dec 14 '20 at 15:36
Indeed, it is possible. For instance look here https://sfmagazine.com/post-entry/july-2018-excel-reporting-text-in-a-pivot-table/ . Problem is I don't have a variable to add in columns. All the examples I found are like this. Usually a variable with dummy values used in 'Column' of Pivot Table. — coelidonum, Dec 14 '20 at 15:41

score 1 · Accepted Answer · answered Dec 14 '20 at 17:01

1

If you're OK with the CF column coming before Fatturato, then you can just stick it on the rows and use Show in Tabular Form under Report Layout dropdown on the Design tab.

Not a great general solution but might be a good option for some situations.

answered Dec 14 '20 at 17:01

Alexis Olson

38,724
7
42
64

thank you!! I missed that option in that dropdown tab – coelidonum Dec 14 '20 at 17:44

score 0 · Answer 2 · answered Dec 14 '20 at 16:57

0

If the values in column CF are always the same then you can use LASTNONBLANK in a measure and drag that to your values field

CF:=CALCULATE(LASTNONBLANK(Tabella1[CF],0))

answered Dec 14 '20 at 16:57

Jody Highroller

999
5
12

Showing the a column as text in Power Pivot Table next to a summarized variable (Power Pivot)

2 Answers2