Calculate amount based on range mapping for each line item

[Vinothsusai]

Hi,

I have a two tables below and statutory table defined ranging in gross accounts column. For ex: 109*  can be 10910000, 10920000. It can be any range mapping in ledger table Main accounts column defined below. I want to calculate amount based on the range mappings for each line items.Could you please advise.

Statutory Table

Debitor	Gross Accounts	Amount
Capital souscrit non appelé (I)	109*
ACTIF IMMOBILISÉ*
IMMOBILISATIONS INCORPORELLES
Frais d'établissement*	201*
Frais de recherche et de développement*	203*
Concession, brevets et droits similaire	205*
Fonds commercial (1)	206*+207*
Autres immobilisations incorporelles	208*
Avances et acomptes sur immobilisations incorporelles	237*

Ledger Table

MainAccount	Amount
10910000	2000
10920000	1700
20100000	2300
20100001	40000
20100002	36777
20310000	33232
20300001	1211
20300002	3232
20500000	322
20510000	434
20520000	8989
20600000	786868
20600001	898989
20600002	6767
20611002	67767
20710000	675676
20700001	65656
20700002	56456
20711002	66767
20800000	89898
20800001	32323
20800002	3434
23700001	2332
23700003	32323

Please advise.

Thanks

Vinoth SUSAINATHAN

[v-lionel-msft · Accepted solution]

Hi @Vinothsusai ,

You can achieve in "Edit Query":

1. Add a custom column in "Statutory Table" , then Split the custom column by position:

You will get a table like this:

2. Do the same for "Ledger Table ", But one more step: Replace the value 207 with 206 .

3. create relationship between the two tables:

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

There are a few ways. If there not many values try this

From gross column create three-digit code, for 201* = 201 and for 206* 207* you can take only one.

On the other side, Main Account creates a three-digit code using the left function. Put a logic to convent 207 to 206 and for similar fields.

Now you can join.

Hope this can help.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601