How Could I do that?

[quickbi]

I have a table with values classified in account,Level1 level2.

I want to sum values using this formula:

PL Totals = 
SWITCH(
    TRUE(),
    MAX('Vicio Accounts'[Level1] = "2.Gross Margin",CALCULATE(SUM('PL Snapshot'[balance])/-1000,'Vicio Accounts'[Level1]="1.Net Revenue")+CALCULATE(SUM('PL Snapshot'[balance])/-1000,'Vicio Accounts'[Level1]="2.Gross Margin"))
    

    ,[PL Amount] 
)

but I received an error:

A single value for column 'Level1' in table 'Vicio Accounts' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

What I want to do is a sum of these two levels:

any idea how to do that?

thanks

[quickbi]

Thank you very much @Shivu-2000 

[Shivu-2000 · Accepted solution]

Hi @quickbi,
You cannot use direct column names in the measure, to use column you will have to use the aggregation like min, max as specified in error.

Here's how to fix the issue:

1. Change the Level of Detail:

   • You can modify the context of your calculation using the ALL or EXCEPT functions within SWITCH.

   For instance:

   PL Totals = 
   SWITCH(
       TRUE(),
       ALL('Vicio Accounts'[Level1]),  // Ensures context includes all rows in 'Level1'
       MAX('Vicio Accounts'[Level1]) = "2.Gross Margin",
           CALCULATE(SUM('PL Snapshot'[balance])/-1000,'Vicio Accounts'[Level1]="1.Net Revenue")+CALCULATE(SUM('PL Snapshot'[balance])/-1000,'Vicio Accounts'[Level1]="2.Gross Margin")
   
       ,[PL Amount] 
   )

   This approach ensures the SWITCH function considers all rows in 'Level1' when evaluating the condition.

2. Aggregate Inside the SWITCH:

   • Another option is to introduce aggregation functions (SUM, MAX, MIN etc.) within the SWITCH condition itself, directly on the 'Level1' column.

   For example:

   PL Totals = 
   SWITCH(
       TRUE(),
       MAX('Vicio Accounts'[Level1]) = "2.Gross Margin",  // Checks if MAX of 'Level1' is "2.Gross Margin"
           CALCULATE(SUM('PL Snapshot'[balance])/-1000,'Vicio Accounts'[Level1]="1.Net Revenue")+CALCULATE(SUM('PL Snapshot'[balance])/-1000,'Vicio Accounts'[Level1]="2.Gross Margin")
   
       ,[PL Amount] 
   )

   This way, the SWITCH function evaluates a single aggregated value from 'Level1' instead of the entire column.

Choose the approach that best suits your scenario.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Happy to help!