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,
Is there someone who can help me by the following.
I have the folowing table:
Table: Salesman | ||||
Region | Salesman | Sales Budget | Margin Index | Margin Budget |
A | A1 | 1.000.000,00 | 50 | 500.000,00 |
A | A2 | 2.000.000,00 | 60 | 1.200.000,00 |
A | A3 | 2.500.000,00 | 55 | 1.375.000,00 |
B | B1 | 2.000.000,00 | 60 | 1.200.000,00 |
B | B2 | 4.000.000,00 | 45 | 1.800.000,00 |
B | B3 | 3.000.000,00 | 55 | 1.650.000,00 |
C | C1 | 1.500.000,00 | 55 | 825.000,00 |
C | C2 | 2.000.000,00 | 50 | 1.000.000,00 |
C | C3 | 6.000.000,00 | 40 | 2.400.000,00 |
Total | 24.000.000,00 | 11.950.000,00 | ||
Region A AVG Margin Index | 55,91 | |||
Region B AVG Margin Index | 51,67 | |||
Region C AVG Margin Index | 44,47 | |||
Total AVG Margin Index | 49,79 |
And I want to put it in a matrix like the one below:
Subtotals by region and a grand total
1) Sales Budget is a SUM
2) Margin Budget is a Sum
3) Margin Index is a weighted AVERAGE (this is the one I am fighting with !!!!)
Requested Visual Power BI Matrix | ||||
Region | Salesman | Sales Budget | Margin Index | Margin Budget |
A | 5.500.000,00 | 55,91 | 3.075.000,00 | |
A1 | 1.000.000,00 | 50,00 | 500.000,00 | |
A2 | 2.000.000,00 | 60,00 | 1.200.000,00 | |
A3 | 2.500.000,00 | 55,00 | 1.375.000,00 | |
B | 9.000.000,00 | 51,67 | 4.650.000,00 | |
B1 | 2.000.000,00 | 60,00 | 1.200.000,00 | |
B2 | 4.000.000,00 | 45,00 | 1.800.000,00 | |
B3 | 3.000.000,00 | 55,00 | 1.650.000,00 | |
C | 9.500.000,00 | 44,47 | 4.225.000,00 | |
C1 | 1.500.000,00 | 55,00 | 825.000,00 | |
C2 | 2.000.000,00 | 50,00 | 1.000.000,00 | |
C3 | 6.000.000,00 | 40,00 | 2.400.000,00 | |
Total | 24.000.000,00 | 49,79 | 11.950.000,00 |
Hi, this post could be helpful in your case.
http://blog.iwco.co/2020/01/14/calculos-ponderados-dax/
Regards
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 |
---|---|
40 | |
20 | |
19 | |
15 | |
15 |
User | Count |
---|---|
47 | |
28 | |
25 | |
17 | |
17 |