cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
paolomint
Helper II
Helper II

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

 

1 ACCEPTED SOLUTION
v-xiaotang
Community Support
Community Support

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.

v-xiaotang_0-1620811445727.png

 

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

 

Result:

v-xiaotang_1-1620811445728.png

 

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:

v-xiaotang_2-1620811445733.png

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.

View solution in original post

2 REPLIES 2
v-xiaotang
Community Support
Community Support

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.

v-xiaotang_0-1620811445727.png

 

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

 

Result:

v-xiaotang_1-1620811445728.png

 

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:

v-xiaotang_2-1620811445733.png

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.

View solution in original post

joshyT7
Frequent Visitor

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors