cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Need syntax help to summarize only the selected column

I have three columns in a table and we are using parameters to select the required column to the table.
Based on the selected column, I need to summarize and do some calculation.
I am stuck and unable to find how to make “VAR SUMMERISE” to work, this variable should summerize the selected columns only. I am using this to find the denominator.
It works fine when all three columns are selected, but I need to summerize based on the selected column
Parameter are [column names]: We have a slicer on the page

🔲Partnername

☑️partnerTYPE

☑️partnercountry
I need a syntax to summarize just the selected columns

```Uniques =
VAR sumfc = SUM(salary)
VAR SUMMARISE = SUMMARIZE(Partnername, Partnercountry, partnerTYPE, "Average", sumfc)
RETURN
IF(
OR(
ISINSCOPE('Partnername'),
OR(ISINSCOPE('Partnercountry'), ISINSCOPE(partnerTYPE))
),
SUM(salary),
DIVIDE(sumfc, COUNTROWS(SUMMARISE))
)```
5 REPLIES 5
Frequent Visitor

@Sahir_Maharaj  Thanks for replying, but I am trying both the solution and still getting this error
"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

Super User

Hello @SwathiGanesh,

You can use the SWITCH function to check which column is selected.

``````Uniques =
VAR SelectedColumn = SELECTEDVALUE('Table'[Parameter])
VAR sumfc = SUM('Table'[Salary])
VAR SUMMARISE =
SWITCH(
SelectedColumn,
"Partnername", SUMMARIZE('Table', 'Table'[Partnername], "Average", sumfc),
"partnerTYPE", SUMMARIZE('Table', 'Table'[partnerTYPE], "Average", sumfc),
"partnercountry", SUMMARIZE('Table', 'Table'[partnercountry], "Average", sumfc)
)
RETURN
IF(
OR(
ISINSCOPE('Table'[Partnername]),
OR(ISINSCOPE('Table'[partnerTYPE]), ISINSCOPE('Table'[partnercountry]))
),
SUM('Table'[Salary]),
DIVIDE(sumfc, COUNTROWS(SUMMARISE))
)``````

Should you require further assistance, please do not hesitate to reach out.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 13K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning
Frequent Visitor

How to switch when two columns are selected and I need to summarize into one table

Super User

@SwathiGanesh, you can use the UNION function to combine the data from both columns into a single table.

``````UnionTable =
UNION(
SELECTCOLUMNS(Table1, "Column1", [Column1], "Column2", BLANK()),
SELECTCOLUMNS(Table2, "Column1", BLANK(), "Column2", [Column2])
)``````

The SELECTCOLUMNS function is used to select the columns from each table that you want to include in the UnionTable.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 13K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

Jeg setter stor pris på hjelpen!

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors