Forum Discussion

TCavins's avatar
TCavins
Icon for Helper V rankHelper V
6 years ago
Solved

Calculated Table UNION and SUMMARIZE?

I have a table and having trouble with 5 columns. Lets call them Column1, Column2, Column3, Column4 and Column5 for this example.

 

I have the columns in a pie chart but I want a custom tooltip to display all 5 values (Column Name and count where it equals "Yes").

 

ColumnName   TotalCount

Column1           49

Column2           13

Column3           67

Column4           24

Column5           43

 

I'm having trouble figuring out the best way to do this. I've tried doing a calculated table UNION/SUMMARIZE to create two columns with the columnname and count as shown above but it errors out and I'm not sure how to do it.

 

Any help?

 

Need Help
  • TCavins's avatar
    TCavins
    6 years ago

    I was missing the part where I'd hardcode the column into the ColumnName field as shown in the snippet below.

    "ColumnName","Column1"
...
"ColumnName","Column2"

     

    I added the grouping of CalendarID which is used on my main report to filter records and this allows my tooltip to receive the filtered fields too.

    Here's a sample of what I got to work.

     

    NewTable = 
UNION ( 
SUMMARIZE( 
TableName, 
TableName[CalendarID], 
"ColumnName","Column1", 
"Total",COUNTROWS(FILTER(TableName, TableName[Column1] = "Yes" ) ) 
), 
SUMMARIZE( 
TableName, 
TableName[CalendarID], 
"ColumnName","Column2", 
"Total",COUNTROWS(FILTER(TableName, TableName[Column2] = "Yes") ) 
) 
)

     

     

     

2 Replies

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

    Hi TCavins ,

     

    Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive for Business and share the link here.

     

    • TCavins's avatar
      TCavins
      Icon for Helper V rankHelper V
      6 years ago

      I was missing the part where I'd hardcode the column into the ColumnName field as shown in the snippet below.

      "ColumnName","Column1"
...
"ColumnName","Column2"

       

      I added the grouping of CalendarID which is used on my main report to filter records and this allows my tooltip to receive the filtered fields too.

      Here's a sample of what I got to work.

       

      NewTable = 
UNION ( 
SUMMARIZE( 
TableName, 
TableName[CalendarID], 
"ColumnName","Column1", 
"Total",COUNTROWS(FILTER(TableName, TableName[Column1] = "Yes" ) ) 
), 
SUMMARIZE( 
TableName, 
TableName[CalendarID], 
"ColumnName","Column2", 
"Total",COUNTROWS(FILTER(TableName, TableName[Column2] = "Yes") ) 
) 
)