Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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.
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
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.
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?
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.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
98 | |
78 | |
65 | |
60 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |