Unable to calculate grand total. Is it possible?

[Rai_BI]

Hi all,

Is anyone out there a master of DAX? A Shaolin master of the DAX...

I need a DAX measure that calculates the value for each line and for the grand total according to conditionals.

If the "Name Seller 1" is in scope then calculate the measure "Goal Seller 1" for each line and for the grand total.

If the "Name Seller 2" is in scope then calculate the measure "Goal Seller 2" for each line and for the grand total.

If both columns "Name Seller 1" and "Name Seller 2" are in scope then calculate the sum of "Goal Seller 1" and "Goal Seller 2" for each line and for the grand total.

I have the following DAX measure which works perfectly for each line, but don´t work for the grand total...

Single Measure = 

VAR Seller_1_in_Scope = HASONEFILTER('customer table'[Name Seller1])
VAR Seller_2_in_Scope = HASONEFILTER('customer table'[Name Seller2])

RETURN
SWITCH(
    TRUE(),
        Seller_1_in_Scope && NOT Seller_2_in_Scope, 
        SUMX(
            VALUES('customer table'[Name Seller1]),[Goal Seller 1]),

        Seller_2_in_Scope && NOT Seller_1_in_Scope, 
        SUMX(
            VALUES('customer table'[Name Seller2]),[Goal Seller 2]),

        Seller_1_in_Scope && Seller_2_in_Scope, 
        SUMX(
            SUMMARIZE(
                'customer table',
                'customer table'[Name Seller1],'customer table'[Name Seller2]),
                CALCULATE([Goal Seller 1] + [Goal Seller 2])
        )
)

  I´m unable to calculate the grand total for each condition because it is returning blank. Take a look at the print.

How can i fix it?
Download the PBIX file here

[lbendlin]

You can use VALUES() against the Field Parameter to figure out if one, the other, or both are selected.

[AlexisOlson]

Do you need all the scope stuff?

I'd think you could write the measure much more simply:

VAR _Goal1 =
    CALCULATE (
        SUM ( goals[Goal] ),
        USERELATIONSHIP ( 'customer table'[ID Seller1], goals[Seller ID] )
    )
VAR _Goal2 =
    CALCULATE (
        SUM ( goals[Goal] ),
        USERELATIONSHIP ( 'customer table'[ID Seller2], goals[Seller ID] )
    )
RETURN
    _Goal1 + _Goal2

[Rai_BI]

No, i need a DAX measure that calculates the value for each line and for the grand total according to conditionals.

If the "Name Seller 1" is in scope then calculate the measure "Goal Seller 1" for each line and for the grand total.

If the "Name Seller 2" is in scope then calculate the measure "Goal Seller 2" for each line and for the grand total.

If both columns "Name Seller 1" and "Name Seller 2" are in scope then calculate the sum of "Goal Seller 1" and "Goal Seller 2" for each line and for the grand total.

[lbendlin]

ISINSCOPE is largely meaningless for table visuals. Everything is always in scope.

Use Matrix visuals or other visuals that support hierarchies.

[Rai_BI]

@lbendlin I have a field parameter applied to the table with several options to add to the visual such as products, categories, months, dates and also among these options are the columns "Name Seller 1" and "Name Seller 2".

[lbendlin]

Aha, field parameters! That was unclear to me from your initial explanation. Well, bad news.  The order of selection of field parameter values can not be probed as far as I know.  Happy to be proven wrong.

[Rai_BI]

That's why I'm trying to check if the column is in the visual so I can make the correct calculation.

[Rai_BI]

I already tried that and it didn't work

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Rai_BI]

I already provide a PBIX file in my topic here

[lbendlin]

Am I missing something?

[Rai_BI]

It is not right. You are simply adding the two measurements together. First you need to check

If the "Name Seller 1" is in scope then calculate the measure "Goal Seller 1" for each line and for the grand total 

OR 

If the "Name Seller 2" is in scope then calculate the measure "Goal Seller 2" for each line and for the grand total 

OR 

If both columns "Name Seller 1" and "Name Seller 2" are in scope then calculate the sum of "Goal Seller 1" and "Goal Seller 2" for each line and for the grand total.

you can only sum [Goal Seller 1] + [Name Seller 2] If both columns "Name Seller 1" and "Name Seller 2" are in the table visual

[lbendlin]

We talked about this in the other thread. The concept of scope is not applicable to flat tables. Everything is always in scope.  Maybe you can describe your requirement in different words.

[Rai_BI]

I need to create a DAX measure that calculates the sales value depending on which table column is applied to the table visual. In other words, i need to check whether a specified column is applied to the visual and then calculate the corresponding values.

I have a table that is basically a customer portfolio and this table contains the fields "Customer Name", "Seller Name 1" and "Seller Name 2". Each customer is served by two types of salespeople, salesperson 1 and salesperson 2.

If the "Name Seller 1" column is applied to the visual then calculate the "Goal Seller 1" measure for each row of the visual and also for the grand total.

If the "Name Seller 2" column is applied to the visual then calculate the "Goal Seller 2" measurement for each row of the visual and also for the grand total.

If both columns "Name Seller 1" and "Name Seller 2" are applied to the visual, then calculate the sum of the two measures "Name Seller 1" and "Name Seller 2" for each row and also for the grand total.

See the measurement I took below using the SWITCH function, it checks the three conditionals and correctly calculates the values for each line, but the grand total value is returning empty instead of the total of the condition that is being satisfied

Single Measure = 

VAR Seller_1_in_Scope = HASONEFILTER('customer table'[Name Seller1])
VAR Seller_2_in_Scope = HASONEFILTER('customer table'[Name Seller2])

RETURN
SWITCH(
    TRUE(),
    Seller_1_in_Scope && NOT Seller_2_in_Scope, 
    SUMX(
        VALUES('customer table'[Name Seller1]),[Goal Seller 1]),

    Seller_2_in_Scope && NOT Seller_1_in_Scope, 
    SUMX(
        VALUES('customer table'[Name Seller2]),[Goal Seller 2]),

    Seller_1_in_Scope && Seller_2_in_Scope, 
    SUMX(
        'customer table',
        CALCULATE([Goal Seller 1] + [Goal Seller 2])
)
)

I need to ensure that the grand total returns the sum of one of the three conditions.