Sum of values in a column based on another column logic using DAX/Power Query

[Bhanu_VA]

I have a scenario like below. I have to get the consumption value against each row meeting the nearest value criteria. there is a possibility of two cases;

Case1: (for the Cycle group)  I have to sum the values until I reach the nearest value(10) of each group. For the below example, the sum of 3,2,4 is 9 which is closer to value 10, than the sum of 3,2,4,7 which is 16.  As 9 is the nearest, I need to sum the values in the consumption column and print in the output column. Please see the output table for reference
Input Table:

S No	Product	Consumption	Qty
1	Cycles	4.5	3
2	Cycles	3	2
3	Cycles	6	4
4	Cycles	8.3	7
5	Scooter	7	2
6	Scooter	16	5
7	Scooter	14	4

Output: So the output for row 1 will be like the below

S No	Product	Consumption	Qty	Output
1	Cycles	4.5	3	13.5
2	Cycles	3	2
3	Cycles	6	4
4	Cycles	8.3	7
5	Scooter	7	2
6	Scooter	16	5
7	Scooter	14	4

Case 2: (for the Scooter group) I have to sum the values until I reach the nearest value(10) of each group. For the below example, the sum of 2,5 is 7 which is not closer to value 10, as the sum of 2,5,4 which is 11. As 11 is the nearest, I need to sum the values in the consumption column and print in the output column.
Output:

S No	Product	Consumption	Qty	Output
1	Cycles	4.5	3	13.5
2	Cycles	3	2
3	Cycles	6	4
4	Cycles	8.3	7
5	Scooter	7	2	37
6	Scooter	16	5
7	Scooter	14	4

Thanks in advance for the help.

[Bhanu_VA]

Hello @tamerj1 , As I said your query is perfectly working fine. But when I run the same query on a dataset with 7 million rows, I am getting out of memory issues. Tried with the column and measure as well.

can we do optimization we can do this?

Thanks,

bhanu

[tamerj1]

Hi @Bhanu_VA 

Output Column = 
VAR CurrentSN = 'Table'[S No]
VAR CurrentProductTable = CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Product] ) )
VAR FirstSN = MINX ( CurrentProductTable, 'Table'[S No] )
VAR T1 = 
    GENERATE ( 
        'Table', 
        VAR CurrentSNO = 'Table'[S No]
        VAR T2 = FILTER ( CurrentProductTable, 'Table'[S No] <= CurrentSNO )
        VAR Consumption = SUMX ( T2, 'Table'[Consumption] )
        VAR Qty = SUMX ( T2, 'Table'[Qty] )
        RETURN
            ROW ( "@Consumption", Consumption, "@Qty", Qty )
    )
VAR Result =
    MAXX (
        TOPN ( 1, T1, ABS ( [@Qty] - 10 ), ASC ),
        [@Consumption]
    )
RETURN
    IF ( CurrentSN = FirstSN, Result )

If you want to sjow only for the first row then use the following

Output Column = 
VAR T1 = 
    ADDCOLUMNS ( 
        'Table', 
        "@Qty", 
        VAR CurrentSNO = 'Table'[S No]
        VAR T2 = FILTER ( 'Table', 'Table'[S No] <= CurrentSNO )
        RETURN
            SUMX ( T2, 'Table'[Qty] )
    )
VAR Result =
    MAXX (
        TOPN ( 1, T1, ABS ( [@Qty] - 10 ), ASC ),
        [@Qty]
    )
RETURN
    IF ( 'Table'[S No] = 1, Result )

[Bhanu_VA]

Hello @tamerj1 , Thanks for the immediate solution the query works as well, but there are tweaks required. Will elaborate more below with better examples.

Source Table: 

S No	Product	Consumption	Qty
1	Cycles	4.5	3
2	Cycles	3	2
3	Cycles	6	4
4	Cycles	8.3	7
5	Scooter	7	2
6	Scooter	16	5
7	Scooter	14	4

I have to sum the values(Qty Column) until I reach the nearest value(10) for each group (product) and once the Qty sum reaches the nearest value of 10, then I need to sum up the values in the consumption Column Values and print the value for each row.

For example, for the first row, we need to sum 3,2,4 values in the Qty column, as 9 is the closet number to 10, So I need to sum the 4.5,3,6 values in the consumption column and print next to the first row, the calculation is not only for the first of the group, this should be done for each and every row in the dataset.

Attached is the output table below.

Output:

S No	Product	Consumption	Qty	Output
1	Cycles	4.5	3	13.5
2	Cycles	3	2	9
3	Cycles	6	4	6
4	Cycles	8.3	7	8.3
5	Scooter	7	2	37
6	Scooter	16	5	30
7	Scooter	14	4	14

Thank You.

[tamerj1 · Accepted solution]

@Bhanu_VA 

Please refer to attached sample file with the proposed solution

Output Column = 
VAR CurrentSN = 'Table'[S No]
VAR CurrentProductTable = CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Product] ) )
VAR T1 = 
    ADDCOLUMNS ( 
        CurrentProductTable, 
        "@Qty",
        VAR CurrentSNO = 'Table'[S No]
        VAR T2 = FILTER ( CurrentProductTable, 'Table'[S No] <= CurrentSNO )
        VAR Consumption = SUMX ( T2, 'Table'[Consumption] )
        RETURN
            SUMX ( T2, 'Table'[Qty] )
    )
VAR NearestTo10SN =
    MAXX (
        TOPN ( 1, T1, ABS ( [@Qty] - 10 ), ASC ),
        'Table'[S No]
    )
VAR Result = 
    SUMX ( 
        FILTER ( CurrentProductTable, 'Table'[S No] <= NearestTo10SN && 'Table'[S No] >= CurrentSN ),
        'Table'[Consumption]
    )
RETURN 
    COALESCE ( Result, 'Table'[Consumption] )

[Bhanu_VA]

Thanks a ton, @tamerj1 , the above DAX query worked perfectly 🙂