topic Re: Calculate a value depending on which column is applied to the visual in DAX Commands and Tips

Calculate a value depending on which column is applied to the visual

Rai_BI — Mon, 19 Feb 2024 14:14:45 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.
If you know of another way to achieve this goal please let me know.

DOWNLOAD PBIX HERE

Re: Calculate a value depending on which column is applied to the visual

123abc — Mon, 19 Feb 2024 13:23:49 GMT

To ensure that the grand total returns the sum of one of the three conditions, you can modify your DAX measure. The issue you're facing stems from the fact that when you apply both columns ("Name Seller 1" and "Name Seller 2") to the visual, the HASONEFILTER function does not return true for either of them, causing the grand total to be empty. To address this, you can introduce additional logic to handle the grand total calculation.

Here's the modified DAX measure:

Single Measure =
VAR Seller_1_in_Scope = HASONEFILTER('customer table'[Name Seller 1])
VAR Seller_2_in_Scope = HASONEFILTER('customer table'[Name Seller 2])

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

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

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

// Handle the case when neither Seller 1 nor Seller 2 is in scope
SUMX(
'customer table',
IF(
Seller_1_in_Scope || Seller_2_in_Scope,
0, // Return 0 if any one of the columns is in scope
[Goal Seller 1] + [Goal Seller 2] // Return the sum otherwise
)
)
)

In this modified measure, we added an additional condition in the SWITCH statement to handle the scenario when neither Seller 1 nor Seller 2 is in scope. This is achieved by checking if either Seller_1_in_Scope or Seller_2_in_Scope is true. If either of them is true, it means that one of the columns is in scope, and in such cases, the measure returns 0 for the grand total. Otherwise, it returns the sum of "Goal Seller 1" and "Goal Seller 2".

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Re: Calculate a value depending on which column is applied to the visual

Rai_BI — Mon, 19 Feb 2024 14:01:55 GMT

Sorry but your measurement doesn't work well. Unlike my measure, your measure is returning a value for the grand total, but it is returning an incorrect single value because it is returning the same value regardless of which column is applied to the visual.

Re: Calculate a value depending on which column is applied to the visual

Anonymous — Tue, 20 Feb 2024 07:40:04 GMT

Hi, Rai_BI

In Power BI, if your measure doesn't show totals, it might be because your measure calculation logic doesn't allow it to add up on all rows.

Perhaps you can use the following DAX expression:

Single Measure test = // When the both columns are in scope i need to display the sum of the two measures for each line and at grid total. VAR Seller_1_in_Scope = HASONEFILTER('customer table'[Name Seller1]) VAR Seller_2_in_Scope = HASONEFILTER('customer table'[Name Seller2]) RETURN IF(Seller_1_in_Scope||Seller_2_in_Scope, 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]) )), CALCULATE([Goal Seller 1]+[Goal Seller 2]))

Here is my preview:

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data)

Best Regards

Yongkang Hua

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