Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |