Forum Discussion

SINGHTX9's avatar
SINGHTX9
Icon for Helper II rankHelper II
7 years ago

How to transpose columns in a calculated table

Hi all 

 

I m trying to create a pie chart with 3 columns (CC alone, IA alone and IA and CC )and it is not displaying the data correctly. Could it be because my data is spread across multiple column?

 

So i was trying to transpose multiple columns (CC alone and IA alone and IA and CC) but because this data is in a calculated table whenever i open the query editor it return me back to the actual table.

 

How can i solve this problem? Also how can you create relationship between your actual table and a calculated table or is it not needed?

 

 

Need Help

15 Replies

  • Chihiro's avatar
    Chihiro
    Icon for Solution Sage rankSolution Sage
    7 years ago

    May be use UNION(SELECTCOLUMNS(),....) to create another table.

     

    Something like...

    NewTable =
UNION (
    SELECTCOLUMNS (
        TableName,
        "NewColumnName", "CC alone",
        "ValueColumnName", TableName[CC alone]
    ),
    SELECTCOLUMNS (
        TableName,
        "NewColumnName", "IA alone",
        "ValueColumnName", TableName[IA alone]
    ),
    SELECTCOLUMNS (
        TableName,
        "NewColumnName", "IA and CC",
        "ValueColumnName", TableName[IA and CC]
    )
)

    Note: "NewColumnName" & "ValueColumnName" should be kept consistent across all SELECTCOLUMNS.

    • v-frfei-msft's avatar
      v-frfei-msft
      Icon for Community Support rankCommunity Support
      7 years ago

      Hi SINGHTX9, 

       

      I made one sample using the formula as Chihiro shared, it worked well.

       

      NewTable = 
UNION (
    SELECTCOLUMNS (
        Table1,
        "NewColumnName", "CC alone",
        "ValueColumnName", Table1[CC alone]
    ),
    SELECTCOLUMNS (
        Table1,
        "NewColumnName", "IA alone",
        "ValueColumnName", Table1[IA and CC]
    ),
    SELECTCOLUMNS (
        Table1,
        "NewColumnName", "IA and CC",
        "ValueColumnName", Table1[IA and CC]
    )
)

       

      For more details, please check the pbix as attached.

       

      Regards,

      Frank

      • SINGHTX9's avatar
        SINGHTX9
        Icon for Helper II rankHelper II
        7 years ago

        Thank for the solution Chihiro. It solved my issue partially as id did get the pie chart that i wanted. Now i wanted to filter this by 

        Priority column which is in my Actual data and I m not able to create a relationship between the two. How can i resolve it.

         

        Let me explain the steps i did: My actual data has a field Priority. Then i created a final table as below:

         

        Final Table = ADDCOLUMNS(DISTINCT('Actual data'[Accession  ]),"CCS",SUMX(RELATEDTABLE('Actual data'),'Actual data'[CC]),"IAS",SUMX(RELATEDTABLE('Actual data'),'Actual data'[IA]))

         

        Then i created a new table as you suggested called specimen distribution.

         

        So when i add the priority field in the legend it says cant display visual becasue there is no relation. Do you know how i can edit my final table to incorporate the Priority field in it also?