Forum Discussion
Implementing allocation logic in Power BI
Hi All,
I have to create a solution in Power BI (it's even fine to combine other Power Platform components, i.e., Power Apps & Power Automate if required). The source data contains sales amounts for different categories. Transactions with category 2 indicate that the payments for those transactions are confirmed and hence POS certificates are generated. The requirement is to allocate the amounts and certificate numbers to the category 1 transactions.
Mock up data as visible on a Power BI table visual:
Here, the total confirmed payment (category 2) is 200+300 = 500, so the ask is to allocate this 500 to the transactions with category 1. So I am looking for an output table visual in Power BI (for category 1 transactions), which should look like below after allocation:
In case it's not clear already, let me explain the logic here. These are all the transactions with category 1. For the first transaction, the quantity is 23 and our balance to allocate is 500. So the entire transaction amount can be attributed to the first payment certificate (123). For the sencond transaction, now the balance is 477 (500 - 23), and the transaction quantity is 56. So again, this can be assigned to the POS1 certificate, and so on.
The challenge is, there can be multiple confirmed payments (and hence certificates), and for each one, a new column has to appear as highlighted above, along with the Certificate column which should show the certificate number assigned to a transaction (There can be an 'Allocate' button using which end users can trigger the process. Ultimately, data from this new table would be exported and fed to an external application).
I am not sure whether this kind of a logic can be implemented or not in the first place (for Power BI not having efficient ways to implement loops). Would appreciate any help & suggestions.
parry2k Greg_Deckler amitchandak Idrissshatila lbendlin HarishKM foodd Ritaf1983 Ashish_Mathur danextian aj1973 ToddChitt eliasayyy tamerj1 GilbertQ JoeBarry Vijay_A_Verma
4 Replies
rishirajdeb , I doubt with current columns, I can drive the logic. Unless there are other columns to help, like date , id etc.
amitchandak Actually there would be other columns available in the source data. Let me update the first table with date and unique id (though an unique identifier would not be available by default, I think I should be able to create one composite key if required):
Please let me know if it works, thanks for the help!!
Also, ideally the allocation should start from the earliest transaction date.
Attaching the data for convenience.
Source table:
country category quantity POS transaction date transaction id USA 1 23 02-05-2023 5064 USA 1 56 02-05-2023 5406 USA 1 78 03-05-2023 1878 USA 1 43 03-05-2023 6842 USA 1 200 09-05-2023 3181 USA 1 100 09-05-2023 6259 USA 2 200 123 09-05-2023 9568 USA 2 300 456 09-05-2023 4930 Required output:
country category quantity POS1 POS2 Certificate USA 1 23 23 123 USA 1 56 56 123 USA 1 78 78 123 USA 1 43 43 123 USA 1 200 200 456 USA 1 100 100 456 If required, an Index column can also be added to the source data.
Thanks.
