How to calculate a value depending on the column in the visual

Rai_BI — Wed, 21 Feb 2024 13:41:05 GMT

Hi! Please, some one help me to solve this issue. I've been trying for a long time and I'm failing

I'm using the fields parameter which allows me to add multiple columns from different dimension tables to the visual.

I need to create a DAX measure that calculates the salesperson's target value depending on which 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.

Take a look

I'm just adding the "Year/"Month" column to the visual. The sum is ok.

Now I'm adding the "Year/"Month" and "Name Seller1" column to the visual. The sum is not correct, because I only need to see the seller1's goal

Now I'm adding the "Year/"Month" and "Name Seller2" column to the visual. The sum is not correct, because I only need to see the seller2's goal

Now I'm adding the "Year/"Month" and "Name Seller 1" and "Name Seller2" column to the visual. The sum is correct but it's because the grand total always returns the sum of the two goals. It doesn't check if it's one of the conditionals of the SWITCH that is active.

In other words, the DAX measure must check whether only one column or both "Seller Name1" and "Seller Name2" columns are in the visual to calculate the correct value according to the presence of the columns.

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

If only the "Name Seller 2" column is applied to the visual, calculate the "Goal Seller 2" measure for each row in the visual and also for the grand total.

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

See below the measure I took using the SWITCH function, it checks the three conditionals and correctly calculates the values of each line, but the overall total value is returning an incorrect sum, as it is always returning the sum of the two measurements "Goal Seller 1" and "Goal Seller 2" are independent of which conditional is being satisfied in the SWITCH function.

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]) ), CALCULATE([Goal Seller 1] + [Goal Seller 2]) )

Re: How to calculate a value depending on the column in the visual

jgeddes — Wed, 21 Feb 2024 19:31:20 GMT

The 'Total' row does not have a Name Seller1 or NameSeller2 context.
You can try the following measure which should give the correct result.

Single Measure v2 = var _s1Selected = CONTAINS(SUMMARIZE(Parameter, Parameter[Parâmetro], Parameter[Parâmetro Campos]), Parameter[Parâmetro], "Name Seller1") var _s2Selected = CONTAINS(SUMMARIZE(Parameter, Parameter[Parâmetro], Parameter[Parâmetro Campos]), Parameter[Parâmetro], "Name Seller2") var _countRows = ISFILTERED(Parameter) var _result = SWITCH( TRUE(), _s1Selected && NOT(_s2Selected) && _countRows, [Goal Seller 1], _s2Selected && NOT(_s1Selected) && _countRows, [Goal Seller 2], [Goal Seller 1] + [Goal Seller 2] ) Return _result

Re: How to calculate a value depending on the column in the visual

Rai_BI — Sun, 25 Feb 2024 14:28:53 GMT

Thank you very much

topic How to calculate a value depending on the column in the visual in DAX Commands and Tips

How to calculate a value depending on the column in the visual

Re: How to calculate a value depending on the column in the visual

Re: How to calculate a value depending on the column in the visual