Implementing allocation logic in Power BI

[rishirajdeb]

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 

[rishirajdeb]

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.

[amitchandak]

@rishirajdeb , I doubt with current columns, I can drive the logic. Unless there are other columns to help, like date , id etc.

[rishirajdeb]

Also, ideally the allocation should start from the earliest transaction date.

[rishirajdeb]

@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!!