Forum Discussion
pivot table column as text
I have a pivot table with "products" combined in to "areas" with a sum column of "quantities". I want to add the "units of measure" (UOM) next to the corresponding quantities. There are various units of measure associated with the different products. When I add the UOM data to the columns it breaks the units in to the separate units of measure columns. How can I get the UOM text (label) in to a column next to the quantities they correspond to?
Add the Range to the Data Model.
Right Click > Add Measure
Write DAX measure as:
=CONCATENATEX(Range,[quantities] & " " & Range[UOM], ", ")Returns:
Then you can just remove Subtotals and Grand Total.
You could also review https://www.sqlbi.com/articles/using-concatenatex-in-measures/ for a more complex application.
4 Replies
Add the Range to the Data Model.
Right Click > Add Measure
Write DAX measure as:
=CONCATENATEX(Range,[quantities] & " " & Range[UOM], ", ")Returns:
Then you can just remove Subtotals and Grand Total.
You could also review https://www.sqlbi.com/articles/using-concatenatex-in-measures/ for a more complex application.
Is there a way to keep the subtotals from showing when the area groupings are collapsed? (see screenshot)
Steven_at_FoxTile -
Using COUNTROWS ( ), as described in https://powerpivotpro.com/2012/01/switching-subtotals-on-off-dynamically-using-dax-in-powerpivot/, you can apply an IF as= IF ( [CountRows] > 1, BLANK (), // Try "Many Included" (or some other descriptor) as string you may like this result better CONCATENATEX ( Range, [quantities] & " " & Range[UOM], ", " ) )Where Measure:
CountRows = COUNTROWS ( Range )
