Forum Discussion

SwathiGanesh's avatar
SwathiGanesh
Frequent Visitor
3 years ago

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))
    )
Need Help

5 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    3 years ago

    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.

    • SwathiGanesh's avatar
      SwathiGanesh
      Frequent Visitor
      3 years ago

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

      • Sahir_Maharaj's avatar
        Sahir_Maharaj
        Super User
        3 years ago

        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.

  • SwathiGanesh's avatar
    SwathiGanesh
    Frequent Visitor
    3 years ago

    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."