Sum or count a column if order rank 1 and order rank 2 has the same pickup date

[Andrew_na_lang]

I have my order table where I ranked all orders according to the pickup date. 
I need to sum the total mobile and laptop contribution if the rank 1 order-id and rank 2 order-id have the same pickup date. 

I am creating a calculated column in my customer table. My customer table and orders table have one to many relationship via the customer id. 

please help! URGENT

[Anonymous]

Hi @Andrew_na_lang ,

Please refer to my pbix file to see if it helps you.

Create a measure.

Measure = VAR _1=CALCULATE(SUM('Table (2)'[LAP CONTRIBUTION]),FILTER(ALL('Table (2)'),'Table (2)'[CUSTOMER ID]=SELECTEDVALUE('Table'[customer id])&&'Table (2)'[PICKUP DATE]=SELECTEDVALUE('Table (2)'[PICKUP DATE])))
VAR _2=COUNTAX(FILTER(ALL('Table (2)'),'Table (2)'[CUSTOMER ID]=SELECTEDVALUE('Table (2)'[CUSTOMER ID])&&'Table (2)'[PICKUP DATE]=SELECTEDVALUE('Table (2)'[PICKUP DATE])),'Table (2)'[PICKUP DATE])
VAR _2A=CALCULATE(MIN('Table (2)'[PICKUP DATE]),FILTER(ALL('Table (2)'),'Table (2)'[CUSTOMER ID]=SELECTEDVALUE('Table (2)'[CUSTOMER ID])))
VAR _3=CALCULATE(MAX('Table (2)'[LAP CONTRIBUTION]),FILTER(ALL('Table (2)'),'Table (2)'[CUSTOMER ID]=SELECTEDVALUE('Table (2)'[CUSTOMER ID])&&'Table (2)'[PICKUP DATE]=_2A))
RETURN 
IF(_2=2,_1,_3)

If it still does not help,Please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

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

[Andrew_na_lang]

@Anonymous  I need a calculated column actually.

[Anonymous]

Hi @Andrew_na_lang ,

Please refer to my pbix file to see if it helps you.

If it still does not help,Please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

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

[Andrew_na_lang]

@Anonymous  Can't I create a calculated column in the customer table instead?

[Anonymous · Accepted solution]

Hi @Andrew_na_lang ,

Create a column.

Column = 
VAR _min_date = CALCULATE(MIN('Table use'[PICKUP DATE]),RELATEDTABLE('Table use'))
VAR _total = CALCULATE(SUM('Table use'[LAP CONTRIBUTION]),'Table use'[PICKUP DATE]=_min_date)
RETURN
_total

If it still does not help,Please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

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

[Andrew_na_lang]

Thank you! It worked.

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.  Also, show the expected result very clearly.  Why are you writing a calculated column formula in the Customer Table.  You should be using a measure in the visual instead.

[Andrew_na_lang]

@Ashish_Mathur 

@Anonymous here are the tables and desired output.

Desired output

 

[Ashish_Mathur]

Hi,

I can help if you need a measure and that is what i think you should opt for.  If my approach suits you, then share the download link of the PBI file.

[Andrew_na_lang]

@Ashish_Mathur the reason why I am creating a calculated column in the customer's table is because I am working on a cohort analysis where I verify whether all customers who had laptops in their first order are still ordering. 

Sharing a pbix file

[Anonymous]

Hi @Andrew_na_lang ,

Please have a try.

Create a measure.

measure =
VAR _1 =
    CALCULATE (
        SUM ( tabel[MOBILE COMTRIBUTION] ),
        FILTER (
            ALL ( table ),
            table[PICKUPDATE] = SELECTEDVALUE ( table[PICKUPDATE] )
                && table[Orderranking] = SELECTEDVALUE ( table[Orderranking] )
        )
    )
VAR _2 =
    CALCULATE (
        SUM ( tabel[LAPTOP COMTRIBUTION] ),
        FILTER (
            ALL ( table ),
            table[PICKUPDATE] = SELECTEDVALUE ( table[PICKUPDATE] )
                && table[Order ranking] = SELECTEDVALUE ( table[Order ranking] )
        )
    )
RETURN
    _1 + _2

How to Get Your Question Answered Quickly 

If it still does not help,Please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

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

[Andrew_na_lang]

@Anonymous here are the tables and desired output.

Desired output