Calculate goals per salesperson using the appropriate relationship.

[Rai_BI]

In Power BI I have a customer table with customer and salesperson data. Each Seller is responsible for their respective customers, that is, each seller is responsible for a group of customers or a portfolio of customers. In this customer table there are the following fields: customer code, customer name, Seller1 code, Seller1 name, Seller2 code, Seller2 name, Seller3 code, Seller3 name. Note that for each customer there are 3 Salespeople responsible for the customer.

I have another table with the goals of each seller and this table contains the fields Month, Seller code, Seller Name

The customer table is related to the target table through the fields codeVendedor1, code Vendedor2 and code Vendedor3.

As you know, only one relationship can remain active, which in this case is the Seller2 code field. The other relationships are inactive and to use them it is necessary to use a relationship function such as the USERELATIONSHIP function.

I need a DAX measure that calculates the goal of each Seller so that, if the name Seller1 is in scope, then it calculates Seller1's goals using the appropriate relationship.

If the name Seller2 is in scope, then calculate Seller2's goals using the appropriate relationship.

I made the following DAX measurement below, but it is working in parts, as the totalizer is returned empty or BLANK, I need the totalizer to respect the sum of the goals of EACH seller.

DAX MEASUREMENT...

SWITCH (
TRUE(),
HASONEVALUE('Customers'[Seller code1]),
CALCULATE(
COALESCE(
SUM('Goals'[GoalsValue]),
0
),
USERELATIONSHIP('Customers'[seller code1], 'Goals'[seller code])
),
HASONEVALUE('Customers'[Seller code2]),
CALCULATE(
COALESCE(
SUM('Goals'[GoalsValue]),
0
),
USERELATIONSHIP('Customers'[Seller code2], 'Goals'[Seller code])
),
HASONEVALUE('Customers'[Seller code3]),
CALCULATE(
COALESCE(
SUM('Goals'[GoalsValue]),
0
),
USERELATIONSHIP('Customers'[Seller code3], 'Goals'[Seller code])
),
0
)

[v-tangjie-msft]

Hi @Rai_BI ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create measures.

Measure 1 = SUMX(VALUES('customer table'[Name Seller1]),[Goals])

Measure 2 = SUMX(VALUES('customer table'[Name Seller2]),[Goals])

Measure 3 = SUMX(VALUES('customer table'[Name Seller3]),[Goals])

(3) Then the result is as follows.

Best Regards,

Neeko Tang

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

[Rai_BI]

Thank you very much, I think we are almost there. However, you created three measurements, I need to use these three measurements in just one.
Because I developed a single report for all sellers, if I use three different measures I will have to make three different reports.

[v-tangjie-msft · Accepted solution]

Hi @Rai_BI ,

We can create a field parameter.

Then we can create a measure.

Measure = 
SWITCH (
    TRUE (),
    SELECTEDVALUE ( Parameter[Parameter Order] ) = 0, SUMX ( VALUES ( 'customer table'[Name Seller1] ), [Goals] ),
    SELECTEDVALUE ( Parameter[Parameter Order] ) = 1, SUMX ( VALUES ( 'customer table'[Name Seller2] ), [Goals] ),
    SUMX ( VALUES ( 'customer table'[Name Seller3] ), [Goals] )
)

If you do not want to display field parameter slicers, you can create a slicer table and a measure.

Slicer = DATATABLE ( 
    "Seller", STRING, 
    {
        { "Name Seller1" },
        { "Name Seller2" },
        { "Name Seller3" }
    }
) 

Measure 4 = 
SWITCH (
    TRUE (),
    SELECTEDVALUE ( 'Slicer'[Seller]) = "Name Seller1", SUMX ( VALUES ( 'customer table'[Name Seller1] ), [Goals] ),
    SELECTEDVALUE ( 'Slicer'[Seller]) = "Name Seller2",SUMX ( VALUES ( 'customer table'[Name Seller2] ), [Goals] ),
    SUMX ( VALUES ( 'customer table'[Name Seller3] ), [Goals] )
)

Edit interactions for each slicer in the format pane and hide the slicers.

Best Regards,

Neeko Tang

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

[Rai_BI]

That's right, thank you very much for your help.😀

[Sahir_Maharaj]

Hello @Rai_BI,

Can you please try this DAX:

SellerGoals := 
VAR IsSeller1InContext = HASONEVALUE('Customers'[Seller code1])
VAR IsSeller2InContext = HASONEVALUE('Customers'[Seller code2])
VAR IsSeller3InContext = HASONEVALUE('Customers'[Seller code3])
RETURN
IF(
    IsSeller1InContext,
    CALCULATE(
        SUM('Goals'[GoalsValue]),
        USERELATIONSHIP('Customers'[Seller code1], 'Goals'[Seller code])
    ),
    IF(
        IsSeller2InContext,
        CALCULATE(
            SUM('Goals'[GoalsValue]),
            USERELATIONSHIP('Customers'[Seller code2], 'Goals'[Seller code])
        ),
        IF(
            IsSeller3InContext,
            CALCULATE(
                SUM('Goals'[GoalsValue]),
                USERELATIONSHIP('Customers'[Seller code3], 'Goals'[Seller code])
            ),
            SUMX(
                VALUES('Customers'[Seller code1]),
                CALCULATE(
                    SUM('Goals'[GoalsValue]),
                    USERELATIONSHIP('Customers'[Seller code1], 'Goals'[Seller code])
                )
            ) + SUMX(
                VALUES('Customers'[Seller code2]),
                CALCULATE(
                    SUM('Goals'[GoalsValue]),
                    USERELATIONSHIP('Customers'[Seller code2], 'Goals'[Seller code])
                )
            ) + SUMX(
                VALUES('Customers'[Seller code3]),
                CALCULATE(
                    SUM('Goals'[GoalsValue]),
                    USERELATIONSHIP('Customers'[Seller code3], 'Goals'[Seller code])
                )
            )
        )
    )
)

[Rai_BI]

Your measurement is basically working like the one I did. It is correctly calculating the individual values, but the total value is being calculated incorrectly.
I am attaching the example pbix file so that you can understand better.
File Download