Hi, I ran into a problem that I don't know how to solve, So I need help if anyone has an idea.
I'll try to describe what I'm doing to help you understand the problem. These are shipments in the post office.
More values appear in the database 1. One person can send multiple packages (Client ID - Multiple package IDs) 2. Several persons can send a combined package (multiple client IDs - one ID package)
Table 1: Example data
| ID | Code_ID | Criterion | Amount_ID | Amount_Code_ID |
| 0024025005057 | 18480000375001 | 01_criterion | 239.837 | 350.110 |
| 0024025005057 | 18480002364001 | 01_criterion | 1.223.166 | 1.198.186 |
| 0024025005111 | 31440025191001 | 01_criterion | 1.719 | 354.398 |
| 0024025005111 | 41450025338001 | 01_criterion | 162.205 | 3.175 |
| 0024173003560 | 51961249242001 | 02_criterion | 64.456 | 22.259 |
| 0024173003578 | 54921203295001 | 02_criterion | 215.848 | 189.222 |
| 0024173003578 | 59891065502001 | 02_criterion | 674.540 | 201.903 |
| 0024173003578 | 59891077907001 | 02_criterion | 650.557 | 465.491 |
| 0024173003578 | 59891082440001 | 02_criterion | 11.442.201 | 11.850.952 |
| 0024173003578 | 62010293763001 | 02_criterion | 211.541 | 7.332 |
| 0024025004255 | SSM26971 | 03_criterion | 26.682 | 7.107 |
| 0024025004255 | SSM27228 | 03_criterion | 31.778 | 2.901 |
| 0024025004271 | SSM27228 | 03_criterion | 21.186 | 5.243 |
| 0024025004271 | SSM27314 | 03_criterion | 15.889 | 4.718 |
| 0024025004271 | SSM27382 | 03_criterion | 33.377 | 7.222 |
| 0024025004271 | SSM27544 | 03_criterion | 9.489 | 5.287 |
| 0024025004271 | SSM378 | 03_criterion | 18.425 | 1.230 |
| 0024025004271 | SSM3825 | 03_criterion | 23.274 | 3.879 |
| 0022002080492 | SSM12593 | 04_SHIPMENT DELIVERED | 34.992 | 54.548 |
| 0022002080492 | SSM12596 | 04_SHIPMENT DELIVERED | 20.338 | 124.823 |
| 0022002084056 | SSM12596 | 04_SHIPMENT DELIVERED | 443.486 | 455.183 |
| 0022002084331 | SSM4705 | 04_SHIPMENT DELIVERED | 23.093 | 500.733 |
| 0024025004255 | SSM26971 | 05_total | 26.682 | 7.107 |
| 0024025004255 | SSM27228 | 05_total | 31.778 | 2.901 |
| 0024025004271 | SSM27228 | 05_total | 21.186 | 5.243 |
| 0024025004271 | SSM27314 | 05_total | 15.889 | 4.718 |
| 0024025004271 | SSM27382 | 05_total | 33.377 | 7.222 |
| 0024025004271 | SSM27544 | 05_total | 9.489 | 5.287 |
| 0024025004271 | SSM378 | 05_total | 18.425 | 1.230 |
| 0024025004271 | SSM3825 | 05_total | 23.274 | 3.879 |
| 0024025005057 | 18480000375001 | 05_total | 239.837 | 350.110 |
| 0024025005057 | 18480002364001 | 05_total | 1.223.166 | 1.198.186 |
| 0024025005111 | 31440025191001 | 05_total | 1.719 | 354.398 |
| 0024025005111 | 41450025338001 | 05_total | 162.205 | 3.175 |
| 0024173003560 | 51961249242001 | 05_total | 64.456 | 22.259 |
| 0024173003578 | 54921203295001 | 05_total | 215.848 | 189.222 |
| 0024173003578 | 59891065502001 | 05_total | 674.540 | 201.903 |
| 0024173003578 | 59891077907001 | 05_total | 650.557 | 465.491 |
| 0024173003578 | 59891082440001 | 05_total | 11.442.201 | 11.850.952 |
| 0024173003578 | 62010293763001 | 05_total | 211.541 | 7.332 |
Table 2: Description of columns
| ID | PACKAGE SENDER ID |
| Code_ID | PACKAGE ID |
| Criterion | CRITERIA WHY THE PACKAGE WAS RETURNED |
| Amount_ID | REPORTED AMOUNT |
| Amount_Code_ID | ESTIMATED AMOUNT |
Table 3: The overview I want to achieve in Power Bi
| Criterion | Sum of Amount_ID | higher value | Participation |
| 01_criterion | 1.626.927 | 1.362.110 | 10,28% |
| 02_criterion | 13.259.143 | 11.873.211 | 89,61% |
| 03_criterion | 180.100 | 14.329 | 0,11% |
| 05_total | 15.066.169 | 13.249.650 | |
Participation - Is the ratio of higher value in 05_Total
Problem Description
I'm currently doing all of this in Excel. The database became huge (over 2 million rows) and the criteria grew to over 100, so manual calculation became too demanding for me.
Table 4: I currently do this via pivot in excel as follows
| Criterion | ID | Sum of Amount_ID | Max of Amount_Code_ID | higher value |
| 01_criterion | 0024025005057 | 1.463.003 | 1.198.186 | 1.198.186 |
| 01_criterion | 0024025005111 | 163.924 | 354.398 | 163.924 |
| 02_criterion | 0024173003560 | 64.456 | 22.259 | 22.259 |
| 02_criterion | 0024173003578 | 13.194.687 | 11.850.952 | 11.850.952 |
| 03_criterion | 0024025004255 | 58.460 | 7.107 | 7.107 |
| 03_criterion | 0024025004271 | 121.639 | 7.222 | 7.222 |
| 05_total | 0024025004255 | 58.460 | 7.107 | 7.107 |
| 05_total | 0024025004271 | 121.639 | 7.222 | 7.222 |
| 05_total | 0024025005057 | 1.463.003 | 1.198.186 | 1.198.186 |
| 05_total | 0024025005111 | 163.924 | 354.398 | 163.924 |
| 05_total | 0024173003560 | 64.456 | 22.259 | 22.259 |
| 05_total | 0024173003578 | 13.194.687 | 11.850.952 | 11.850.952 |
higher value: =+IF(Sum of Amount_ID>Max of Amount_Code_ID;Max of Amount_Code_ID;Sum of Amount_ID)
I need to take a higher value
Table 3 - I get from table 4 by summing Amount_ID and higher value by criteria
Note: There are multiple criteria, I only need to select the criteria that are required of me. The total is not a sum, because it may happen that the package is returned on several grounds, and then the amounts are doubled. For this reason, I have to report the total and I solved that, but now the total appears as a criterion. I don't know how to calculate the percentage, but so I tell him to put it in the ratio 01_criteria/05_Total ... (as in table 3 - Participation)
Thanks in advance for your help, any ideas would be great,
Dejan
