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    ITEM GOAL Shoes 500 Shirt 300 Tie 600   I need to split the goal for each agent based on the % of the clients they sold...
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.

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