Forum Discussion

LorneS's avatar
LorneS
Copper Contributor
Feb 01, 2022
Solved

Pivot table two lines should be one

I am working on a pivot table, the underlying database is tied to a common numeric identifier, and the data spans several years. While the numeric identifier is consistent, the text label associated ...
BI & Data Analysis
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Feb 01, 2022

    LorneS 
    Creating PivotTable add data to data model and add calculated column

    as

    =
VAR agnt = Range[Agent]
RETURN
    CALCULATE (
        CONCATENATEX ( VALUES ( Range[Name] ), Range[Name], ", " ),
        ALL ( Range ),
        Range[Agent] = agnt
    )

    Use it in PivotTable:

  • LorneS's avatar
    LorneS
    Copper Contributor
    Feb 01, 2022
    Thank you. One additional question - the query that you developed, was that written with the Advanced Editor or was there a menu selection and wizard that led to that step in the query?
    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      Feb 01, 2022
      The 2 steps following the ChangedTypes steps were written with the Advanced Editor. Not sure there's an easy way to do something similar with the User Interface only
      I suggest you also consider SergeiBaklan proposal as it might be closer/better from a business standpoint

Resources