Forum Discussion

Steven_at_FoxTile's avatar
Steven_at_FoxTile
Copper Contributor
Oct 29, 2019
Solved

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 uni...
excel
  • ChrisMendoza's avatar
    ChrisMendoza
    Oct 30, 2019

    Steven_at_FoxTile -

    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.

     

ChrisMendoza's avatar
ChrisMendoza
Iron Contributor
Oct 30, 2019

Steven_at_FoxTile -

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.

 

  • Steven_at_FoxTile's avatar
    Steven_at_FoxTile
    Copper Contributor
    Oct 30, 2019

    ChrisMendoza 

     

    Is there a way to keep the subtotals from showing when the area groupings are collapsed?  (see screenshot)

     

    • ChrisMendoza's avatar
      ChrisMendoza
      Iron Contributor
      Oct 30, 2019

      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 )

       

       

       

       

       

       

Resources