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.

     

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