Hello All ,
I would like to ask you whether there is a possibility in Power Pivot Table to create an output where each single row ( defined ) will be calculated by the ration given in the table-constant data .
In below example the sales report which have many amounts assigned to repeatable a/c code would need to be multiplied by ratios provided in the static data table. Generally speaking each found account within the sales report would need to be multiplied by number of ratios. Excluding other accounts.
Could someone tell me if this is feasible to do it in Power Table , if not is there any alternative to create such output ? ( enclosed file )
Thank you for a help in advance.
Jan
Example :
| Sales Report | ||||
| a/c Code | a/c Name | a/c Code 2 | Amount | Description |
| 1083000000 | Account 1 | 1310 | 20,000.00 | Test 1 |
| 1083000000 | Account 1 | 1310 | 1,000.00 | Test 1 |
| 1083000000 | Account 1 | 1310 | 30,000.00 | Test 1 |
| 1083000000 | Account 1 | 1310 | - 64,811.00 | Test 1 |
| 1236000000 | Account 2 | 3400 | - 21,111,111.00 | Test 1 |
| 1236000000 | Account 2 | 3400 | - 14,195,711.00 | Test 1 |
| ( constant data) | |
| a/c Code | Ratio |
| 1083000000 | 6% |
| 1083000000 | 6% |
| 1083000000 | 6% |
| 1083000000 | 6% |
| 1083000000 | 6% |
| 1083000000 | 6% |
| 1083000000 | 6% |
| 1083000000 | 44% |
| 1083000000 | 6% |
| 1083000000 | 6% |
| a/c Code | Ratio |
| 1236000000 | 3% |
| 1236000000 | 3% |
| 1236000000 | 6% |
| 1236000000 | 8% |
| 1236000000 | 5% |
| 1236000000 | 8% |
| 1236000000 | 20% |
| 1236000000 | 38% |
| 1236000000 | 3% |
| 1236000000 | 6% |
| a/c Code | g |
| 1400000000 | 1% |
| 1400000000 | 1% |
| 1400000000 | 2% |
| 1400000000 | 2% |
| 1400000000 | 2% |
| 1400000000 | 4% |
| 1400000000 | 66% |
| 1400000000 | 22% |
Hi Jan,
You can do this with Power Query but not Power Pivot because the data in the sales report will be aggregated into one row for a/c Code 1083000000 before the allocation by the ratios is applied, which might be ok for your needs, but that's not the example you provided.
See file contianing Power Query solution attached (cols U:AA). Also in that file is a Power Pivot PivotTable (cols AC:AJ) containing the allocation rolled up for account 1083000000.
Note: I deleted the account information for the accounts you didn't provide an expected output for so that it was easier to compare the results.
Mynda
Hi Mynda,
Thank you for providing this solution. Just wondering how it would work if in the sales report you would have several amounts . I understand that for each amount you would need to create the allocation ( for each ratio ) ?
My report will have hundreds of lines with different amount thus it would be probably difficult to create allocation for each line .
| a/c Code | a/c Name | a/c Code 2 | Amount | Description | Ratio | Sales Allocation |
| 1083000000 | Account 1 | 1310 | 1000 | Test 1 | 0.0625 | 62.5 |
| 1083000000 | Account 1 | 1310 | 1000 | Test 1 | 0.0625 | 62.5 |
| 1083000000 | Account 1 | 1310 | 1000 | Test 1 | 0.0625 | 62.5 |
| 1083000000 | Account 1 | 1310 | 1000 | Test 1 | 0.0625 | 62.5 |
| 1083000000 | Account 1 | 1310 | 1000 | Test 1 | 0.0625 | 62.5 |
| 1083000000 | Account 1 | 1310 | 1000 | Test 1 | 0.0625 | 62.5 |
| 1083000000 | Account 1 | 1310 | 1000 | Test 1 | 0.4375 | 437.5 |
| 1083000000 | Account 1 | 1310 | 1000 | Test 1 | 0.0625 | 62.5 |
| 1083000000 | Account 1 | 1310 | 1000 | Test 1 | 0.0625 | 62.5 |
| 1083000000 | Account 1 | 1310 | 1000 | Test 1 | 0.0625 | 62.5 |
| 1083000000 | Account 1 | 1310 | 20000 | Test 1 | 0.4375 | 8750 |
| 1083000000 | Account 1 | 1310 | 20000 | Test 1 | 0.0625 | 1250 |
| 1083000000 | Account 1 | 1310 | 20000 | Test 1 | 0.0625 | 1250 |
| 1083000000 | Account 1 | 1310 | 20000 | Test 1 | 0.0625 | 1250 |
| 1083000000 | Account 1 | 1310 | 20000 | Test 1 | 0.0625 | 1250 |
| 1083000000 | Account 1 | 1310 | 20000 | Test 1 | 0.0625 | 1250 |
| 1083000000 | Account 1 | 1310 | 20000 | Test 1 | 0.0625 | 1250 |
| 1083000000 | Account 1 | 1310 | 20000 | Test 1 | 0.0625 | 1250 |
| 1083000000 | Account 1 | 1310 | 20000 | Test 1 | 0.0625 | 1250 |
| 1083000000 | Account 1 | 1310 | 20000 | Test 1 | 0.0625 | 1250 |
| 1083000000 | Account 1 | 1310 | 30000 | Test 1 | 0.0625 | 1875 |
| 1083000000 | Account 1 | 1310 | 30000 | Test 1 | 0.0625 | 1875 |
| 1083000000 | Account 1 | 1310 | 30000 | Test 1 | 0.4375 | 13125 |
| 1083000000 | Account 1 | 1310 | 30000 | Test 1 | 0.0625 | 1875 |
| 1083000000 | Account 1 | 1310 | 30000 | Test 1 | 0.0625 | 1875 |
| 1083000000 | Account 1 | 1310 | 30000 | Test 1 | 0.0625 | 1875 |
| 1083000000 | Account 1 | 1310 | 30000 | Test 1 | 0.0625 | 1875 |
| 1083000000 | Account 1 | 1310 | 30000 | Test 1 | 0.0625 | 1875 |
| 1083000000 | Account 1 | 1310 | 30000 | Test 1 | 0.0625 | 1875 |
| 1083000000 | Account 1 | 1310 | 30000 | Test 1 | 0.0625 | 1875 |
Looking forward for your feedback
Thanks
Jan
Hi Jan,
I'm confused. The data you have pasted in above is the output of the query, so I'm not following the example you're describing. The sales report does have several amounts; 20000,1000 and 30000.
If you add data to the Sales report table in the file I provided (paste it on row 6 so that the table automatically expands), then go to the Data tab and press Refresh All, you will see how the query responds.
Mynda
Hi Mynda,
It works , thank you for your help. May I ask you if there is a possibility to see in the Pivot Table the sales data that is not sum up ? Generally speaking to have the % of ration ( which we already have ) and sales for the other accounts that would be not identified as accounts for ratio to be calculated for.
Thank you for your help in advance
Example
| Sales Report | ||||
| a/c Code | a/c Name | a/c Code 2 | Amount | Description |
| 1083000000 | Account 1 | 1310 | 20,000.00 | Test 1 |
| 1083000000 | Account 1 | 1310 | 1,000.00 | Test 1 |
| 1083000000 | Account 1 | 1310 | 30,000.00 | Test 1 |
| 1083000000 | Account 1 | 1310 | - 64,811.00 | Test 1 |
| 1236000000 | Account 2 | 3400 | - 21,111,111.00 | Test 1 |
| 1236000000 | Account 2 | 3400 | - 14,195,711.00 | Test 1 |
| 1236000000 | Account 2 | 3400 | - 3,189,387.00 | Test 1 |
| 1400000000 | Account 3 | 1870 | - 74,649.00 | Test 1 |
| 1400000000 | Account 3 | 1870 | - 55,175.00 | Test 1 |
| 1400000000 | Account 3 | 1850 | - 1,367,551.00 | Test 1 |
| 1400000000 | Account 3 | 1850 | - 51,811,975.00 | Test 1 |
| 1400000000 | Account 3 | 1850 | 1,367,551.00 | Test 1 |
| 1400000000 | Account 3 | 1850 | - 46,392.00 | Test 1 |
| 1400000000 | Account 3 | 1850 | - 66,756.00 | Test 1 |
| 1400000000 | Account 3 | 1850 | - 39,337.00 | Test 1 |
| 1400000000 | Account 3 | 1850 | - 119,718.00 | Test 1 |
| 1400000000 | Account 3 | 1870 | 3,189,387.00 | Test 1 |
| 1401000000 | Account 4 | 1910 | 1,415.00 | Test 1 |
| 1401000000 | Account 4 | 1910 | 777.00 | Test 1 |
| 1401000000 | Account 4 | 1910 | 281.00 | Test 1 |
| 1401000000 | Account 4 | 1910 | 173.00 | Test 1 |
| 1401000000 | Account 4 | 1910 | 1,933.00 | Test 1 |
| 1401000000 | Account 4 | 1910 | 2,281.00 | Test 1 |
| 1401000000 | Account 4 | 1910 | 371.00 | Test 1 |
| 1401000000 | Account 4 | 1910 | 273.00 | Test 1 |
| 1605000000 | Account 5 | 3122 | 300.00 | Test 1 |
| 5100000000 | Account 6 | 3144 | 800.00 | Test 1 |
| 7010000000 | Account 7 | 3455 | 8,900.00 | Test 1 |
| 5100000000 | Account 7 | 3144 | 87,915.00 | Test 1 |
| 7010000000 | Account 7 | 3455 | 156.00 | Test 1 |
Hi,
If you don't want the pivot to aggregate the data, you will have to add an index column in your source and display that in your pivot, in rows section.
This way, each index will be unique, and there will be no aggregation.
