Row context by agent and by customers

[MarceloDF]

Hi everyone.

I have an Issue:

I need to make a distribution of Sales Agent Salaries by Customer. And the distribution it will depend the amount of sales of each customer of each agent from the total of sales of each agent, i.e.:

Agent "Tomas"

Period April 2022

Salary $10.000

Sales of Tomas: 100.000 units

This agent has 4 customers:

Customer 1 bought 20.000 units

Customer 2 bought 30.000 units

Customer 3 bought 25.000 units

Customer 4 bought 25.000 units

So the distribution of the salary will be:

Customer 1: $10.000 * (20.000 / 100.000) = $2.000

Customer 2: $10.000 * (30.000 / 100.000) = $3.000

Customer 3: $10.000 * (25.000 / 100.000) = $2.500

Customer 4: $10.000 * (25.000 / 100.000) = $2.500

I solved it using a Matrix with Agents first in the "Row" fields, and then each customer inside.

And it is a Matrix like this:

But I want to show it by Customer and a Table shape not Matrix and not by Agent, like this:

Is it possible?

[MarceloDF]

Mmmm ok, didn't work. I will clarify a little bit the explanation.

I created a PBIX in order that it will be more clearly to you to understand the relationship of the tables

PBIX File 

[v-xiaosun-msft]

Hi @MarceloDF ,

In the previous solution for you, I think the "table" of "I want to show it by Customer and a Table shape not Matrix and not by Agent" in your description is the visual shown below.

After looking at your example, I found that the two tables are both in Matrix and the measures you created is consistent with the solution I gave you. If you put them in a "table", it will show the correct result.

Or rather, maybe you want to show it by Customer and a Matrix??

Best Regards,
Community Support Team _ xiaosun

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

[MarceloDF]

Okay, with those screenshots I saw that there is a behavior that I didn't know about that.
I put the second one as a table to be congruent with what I want.
Look, when I go to my example without filter:

But when I choose the customer in the Matrix, the numbers change. Can you explain me this?

[v-xiaosun-msft · Accepted solution]

Hi @MarceloDF ,

Thanks for your reply. That is because they are calculated by different values such as "Total Quantity ALL" and "Total Salaries".

I marked them in the screenshot below.

To solve this, you just need to add a slicer. Put "AgentKey" to the slicer and you will get the expected result.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ xiaosun

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

[MarceloDF]

Sorry for the delay, I was a little bit busy.

The problem is that I add the Agent slicer, but brokes me some other measures that I have in the same report that are calculated by customer.

Also, I have a lot of measures that are being summed by customer, but this measure that is calculated by Agent, is not summing the value of the customer only, it is summing the entire value of the agent.

[v-xiaosun-msft]

Hi @MarceloDF ,

According to your description, here is my solution.

It means that you have a column named Customer ID.

Please create two columns in table.

% = 'Tabelle1'[Quantity]/SUM(Tabelle1[Quantity])

Money by Customer = SUM(Tabelle1[Quantity])*'Tabelle1'[%]

Final output:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ xiaosun

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