Sum of first Order ID / last Order ID / unique Order ID

[iamprajot]

Hi, I need the SUM of the Sales but for below conditions:-

1, Sum of Sales based on First Order ID - means sum only the Sales amount for the first occurence of the Order ID

2, Sum of Sales based on Last Order ID - means sum only the Sales amount for the last occurence of the Order ID

3, Sum of Sales based on Distinct Order ID - means average of the single Order ID and then the Sum

Below is the Sample Table for Office Supplies.

Order ID	Sales
88522	13.01
90193	6362.85
90192	211.15
86838	1164.45
86838	22.23
86838	13.99
86838	14.26
86837	33.47
86839	379.53
86836	18.8
86836	945.99
90031	455.77
90032	231.79
41793	1876.69
42949	293.06
42949	914.29
87651	67.49
87651	2.25
87652	54.78
89199	424.68
89200	40.17
89202	783.55
89203	3838.14
89201	58.68
89201	53.1
91454	647.07
88426	627.04
88425	3267.55
88426	110.19
88075	73.55
88075	29.57
87407	22.85
87408	506.39
87406	4
87406	589.8
87946	1225.6
37537	1239.06
37537	4083.19
37537	4902.38
55713	5718.85
87947	1400.53
87365	1821.89
87364	90.98
87366	10728
90596	3127.69
90597	41.82
90597	2875.72
87175	170.8

[v-danhe-msft · Accepted solution]

Hi @iamprajot,

Based on my test, you can refer to below steps:

1.Create an Index column like the picture below:

2.Create two measures to calculate the First/Last sum value.

First sum value = var t=SUMMARIZE('Table1','Table1'[Order ID],"salenew",LOOKUPVALUE('Table1'[Sales],'Table1'[Order ID],'Table1'[Order ID],'Table1'[Index],CALCULATE(MIN('Table1'[Index]),ALLEXCEPT(Table1,'Table1'[Order ID]))))

return

SUMX(t,[salenew])

Last sum value = var t=SUMMARIZE('Table1','Table1'[Order ID],"salenew",LOOKUPVALUE('Table1'[Sales],'Table1'[Order ID],'Table1'[Order ID],'Table1'[Index],CALCULATE(MAX('Table1'[Index]),ALLEXCEPT(Table1,'Table1'[Order ID]))))

return

SUMX(t,[salenew])

3.Create a new table and a new measure to calculate the average sum value.

New Table = GROUPBY (

        Table1,

        Table1[Order ID],

        "Group by ID", AVERAGEX(  CURRENTGROUP (), Table1[Sales])

)

Average sum value = SUM('New Table'[Group by ID])

4.Create three Card visual and add the related fields and you can see the result.

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/3acx04jiiyahlrx/Sum%20of%20first%20Order%20ID%20last%20Order%20ID%20unique...

Regards,

Daniel He