Forum Discussion

paolomint's avatar
paolomint
Helper III
5 years ago
Solved

split values between not related tables

Hi,

The sales department set some goal for each ITEM 

 

ITEMGOAL
Shoes500
Shirt300
Tie600

 

I need to split the goal for each agent based on the % of the clients they sold to last year

 

From my sales table by dax measures:

 

I calculated the total number of clients of 2020 

I calculated the number of clients for each agent in 2020

Then I calculated the % of client of any agent...

 

TOTAL CLIENTS 2020 = 200

CLIENTS of John 2020 = 20

CLIENTS of Alfred 2020 = 50 

% of Client of Jhon = (20/200)*100 = 10%

% of Client of Jhon = (50/200)*100 = 25%

 

So far no problem, 

now How can I split the goal by the % of clients of the agents?

There is no relation between goal table and agent.

 

This is the results I need to obtain:

 

AGENTITEM%CLIENTSGOAL
JhonShoes1050
JhonShirt1030
JhonTie1060
AlfredShoes25125
AlfredShirt2575
AlfredTie25150

 

I hope everything is clear

Thank you very much

Paolo

 

Need Help
  • v-xiaotang's avatar
    v-xiaotang
    5 years ago

    Hi paolomint 

    Not clear about your data model and table structure, so I create a sample, you may take steps bellow for reference.

    -

    1.create the GoalTable.

     

    GoalTable = SUMMARIZECOLUMNS(ClientsTable[Agent],ItemGoalTable[ITEM])

     

    Result:

     

    2.create the two measures.

     

    Measure_%CLIENTS =
VAR _total =
    CALCULATE ( SUM ( ClientsTable[CLIENTS 2020] ), ALL ( ClientsTable ) )
RETURN
    DIVIDE ( SELECTEDVALUE ( ClientsTable[CLIENTS 2020] ), _total )
    Measure_GOAL =
VAR _CLIENTS =
    CALCULATE (
        [Measure_%CLIENTS],
        FILTER (
            ClientsTable,
            ClientsTable[Agent] = SELECTEDVALUE ( GoalTable[Agent] )
        )
    )
VAR _num =
    CALCULATE (
        SELECTEDVALUE ( ItemGoalTable[GOAL] ),
        FILTER (
            ItemGoalTable,
            ItemGoalTable[ITEM] = SELECTEDVALUE ( GoalTable[ITEM] )
        )
    )
RETURN
    _CLIENTS * _num

     

    Result:

    You can check the sample file I attached below.

     

    Best Regards,

    Community Support Team _ Tang

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

2 Replies

  • joshyT7's avatar
    joshyT7
    Frequent Visitor
    5 years ago

    Hi Paolo, 

    If I understand correctly, you have a sales targets table, and then a table of agents and the number of clients from last year? 


    If the tables aren't connected, you can use a measure to filter the table to a selected value and then calculate your goal. 

    For example, FILTER ( 'TableName', 'TableName'['Item'] = "Shoes" )

    You can use the item in your agent table, if you have one, to filter the target table. 

    Let me know if that makes sense and I can help further if needed! 

     

    Thanks

    Josh

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    5 years ago

    Hi paolomint 

    Not clear about your data model and table structure, so I create a sample, you may take steps bellow for reference.

    -

    1.create the GoalTable.

     

    GoalTable = SUMMARIZECOLUMNS(ClientsTable[Agent],ItemGoalTable[ITEM])

     

    Result:

     

    2.create the two measures.

     

    Measure_%CLIENTS =
VAR _total =
    CALCULATE ( SUM ( ClientsTable[CLIENTS 2020] ), ALL ( ClientsTable ) )
RETURN
    DIVIDE ( SELECTEDVALUE ( ClientsTable[CLIENTS 2020] ), _total )
    Measure_GOAL =
VAR _CLIENTS =
    CALCULATE (
        [Measure_%CLIENTS],
        FILTER (
            ClientsTable,
            ClientsTable[Agent] = SELECTEDVALUE ( GoalTable[Agent] )
        )
    )
VAR _num =
    CALCULATE (
        SELECTEDVALUE ( ItemGoalTable[GOAL] ),
        FILTER (
            ItemGoalTable,
            ItemGoalTable[ITEM] = SELECTEDVALUE ( GoalTable[ITEM] )
        )
    )
RETURN
    _CLIENTS * _num

     

    Result:

    You can check the sample file I attached below.

     

    Best Regards,

    Community Support Team _ Tang

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