cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jackson_Dhorty
Helper III
Helper III

Multiple Share Point List Column Calculation

Hi folks,

 

Need help. I have 2 share point list which I am trying to merge with some calculations.

 

SP list 1 = CustomerDetails

 

Registration Code  (LookUp column)NameDOJTransaction Limit
CTR-456Jacob07/04/20205

 

SP list 2 = Transactions

 

Registration Code  (LookUp column)DateNo. Transactions
CTR-45624/08/20212
CTR-45623/08/20211

 

 

First of all, I need to calculate total number of transactions against each Registration Code in SP list 2. Next, I want show the remaining transaction out of total from column Transaction Limit in  SP list 1.

 

Here is the example what I want to show in a table:

 

Registration CodeTotal TransactionsRemining
 CTR-45632

 

Any help would be appreciated. 

 

Thanks 

1 ACCEPTED SOLUTION

Accepted Solutions
v-bofeng-msft
Community Support
Community Support

Hi @Jackson_Dhorty :

I assume 'Registration Code  (LookUp column)' do not allow multiple selections.

My data source:

vbofengmsft_1-1629856684360.png

 

vbofengmsft_0-1629856674950.png

 

Please try this formula

RenameColumns(
    ShowColumns(
        AddColumns(
            AddColumns(
                GroupBy(
                    AddColumns(
                        Transactions,
                        "TheRegistrationCode",
                        'Registration Code'.Value
                    ),
                    "TheRegistrationCode",
                    "NewGroup"
                ),
                "Total Transactions",
                Sum(
                    NewGroup,
                    'No. Transactions'
                )
            ),
            "Remining",
            LookUp(
                CustomerDetails,
                CustomerDetails[@'Registration Code'].Value = TheRegistrationCode
            ).'Transaction Limit' - 'Total Transactions'
        ),
        "TheRegistrationCode",
        "Total Transactions",
        "Remining"
    ),
    "TheRegistrationCode",
    "Registration Code"
)

The Result:

vbofengmsft_2-1629856704388.png

Best Regards,

Bof

 

 

View solution in original post

2 REPLIES 2
v-bofeng-msft
Community Support
Community Support

Hi @Jackson_Dhorty :

I assume 'Registration Code  (LookUp column)' do not allow multiple selections.

My data source:

vbofengmsft_1-1629856684360.png

 

vbofengmsft_0-1629856674950.png

 

Please try this formula

RenameColumns(
    ShowColumns(
        AddColumns(
            AddColumns(
                GroupBy(
                    AddColumns(
                        Transactions,
                        "TheRegistrationCode",
                        'Registration Code'.Value
                    ),
                    "TheRegistrationCode",
                    "NewGroup"
                ),
                "Total Transactions",
                Sum(
                    NewGroup,
                    'No. Transactions'
                )
            ),
            "Remining",
            LookUp(
                CustomerDetails,
                CustomerDetails[@'Registration Code'].Value = TheRegistrationCode
            ).'Transaction Limit' - 'Total Transactions'
        ),
        "TheRegistrationCode",
        "Total Transactions",
        "Remining"
    ),
    "TheRegistrationCode",
    "Registration Code"
)

The Result:

vbofengmsft_2-1629856704388.png

Best Regards,

Bof

 

 

View solution in original post

Thanks. It solved my problem. Really appreciate it.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,847)