topic Re: Unable to calculate grand total. Is it possible? in DAX Commands and Tips

Unable to calculate grand total. Is it possible?

Rai_BI — Fri, 16 Feb 2024 13:54:28 GMT

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

Re: Unable to calculate grand total. Is it possible?

AlexisOlson — Fri, 16 Feb 2024 17:37:13 GMT

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

Re: Unable to calculate grand total. Is it possible?

Rai_BI — Fri, 16 Feb 2024 18:00:11 GMT

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.

Re: Unable to calculate grand total. Is it possible?

lbendlin — Fri, 16 Feb 2024 18:37:50 GMT

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

Use Matrix visuals or other visuals that support hierarchies.

Re: Unable to calculate grand total. Is it possible?

Rai_BI — Sat, 17 Feb 2024 17:50:14 GMT

I already tried that and it didn't work

Re: Unable to calculate grand total. Is it possible?

lbendlin — Sat, 17 Feb 2024 22:26:39 GMT

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-Forum/ba-p/963216

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/1447523

Re: Unable to calculate grand total. Is it possible?

Rai_BI — Sun, 18 Feb 2024 01:35:17 GMT

I already provide a PBIX file in my topic here

Re: Unable to calculate grand total. Is it possible?

lbendlin — Sun, 18 Feb 2024 16:25:40 GMT

Am I missing something?

Re: Unable to calculate grand total. Is it possible?

Rai_BI — Sun, 18 Feb 2024 16:45:21 GMT

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

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.

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

Re: Unable to calculate grand total. Is it possible?

lbendlin — Sun, 18 Feb 2024 20:46:14 GMT

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.

Re: Unable to calculate grand total. Is it possible?

Rai_BI — Mon, 19 Feb 2024 12:57:44 GMT

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.

Re: Unable to calculate grand total. Is it possible?

Rai_BI — Mon, 19 Feb 2024 14:06:02 GMT

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

Re: Unable to calculate grand total. Is it possible?

lbendlin — Mon, 19 Feb 2024 17:52:38 GMT

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.

Re: Unable to calculate grand total. Is it possible?

Rai_BI — Mon, 19 Feb 2024 19:21:10 GMT

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

Re: Unable to calculate grand total. Is it possible?

lbendlin — Mon, 19 Feb 2024 20:52:35 GMT

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