Want to show total sum by sub-category in one row only

[AshDil]

Hi,

I have data as follows:

ID	CATEGORY	SUB-CATEGORY	VALUE
1001	A	AA	1000
1001	A	AA	1200
1001	B	BB	300
1001	B	BB	2000
1002	C	CC	100
1002	C	CC	4000
1002	D	DD	3500
1003	F	EE	2000

Now I wanted to create a calculated column to show total sub-category sum in one row only

Eg: Under ID = 1001

category = A, B

There are 2 sub-categories AA and BB

Now want to show AA total AA sum = 1000 + 1200 = 2000 in one row 

ID	CATEGORY	SUB-CATEGORY	VALUE	COLUMN
1001	A	AA	1000	2200
1001	A	AA	1200
1001	B	BB	300	2300
1001	B	BB	2000
1002	C	CC	100	4100
1002	C	CC	4000
1002	D	DD	3500	3500
1003	F	EE	2000	2000

Please help me to do it.

Thanks,

AshDil

[danextian]

Hi @AshDil ,

First add ann index column in Power Query. This will serve as a helper column to make only one row in the data show the total value.

Then in the Designer, add this as a calculated column

Column = 
VAR __MIN_INDEX =
    CALCULATE (
        MIN ( MyTable[Index] ),
        FILTER (
            MyTable,
            MyTable[CATEGORY] = EARLIER ( MyTable[CATEGORY] )
                && MyTable[SUB-CATEGORY] = EARLIER ( MyTable[SUB-CATEGORY] )
        )
    )
RETURN
    IF (
        __MIN_INDEX = MyTable[Index],
        CALCULATE (
            SUM ( MyTable[VALUE] ),
            FILTER (
                MyTable,
                MyTable[CATEGORY] = EARLIER ( MyTable[CATEGORY] )
                    && MyTable[SUB-CATEGORY] = EARLIER ( MyTable[SUB-CATEGORY] )
            )
        )
    )

Here's the sample PBIX for your reference: https://drive.google.com/file/d/18FiQn-bj8k6jCmZDMK2xmp6Q88ttYL6s/view?usp=sharing 

[danextian · Accepted solution]

Hi @AshDil ,

First add ann index column in Power Query. This will serve as a helper column to make only one row in the data show the total value.

Then in the Designer, add this as a calculated column

Column = 
VAR __MIN_INDEX =
    CALCULATE (
        MIN ( MyTable[Index] ),
        FILTER (
            MyTable,
            MyTable[CATEGORY] = EARLIER ( MyTable[CATEGORY] )
                && MyTable[SUB-CATEGORY] = EARLIER ( MyTable[SUB-CATEGORY] )
        )
    )
RETURN
    IF (
        __MIN_INDEX = MyTable[Index],
        CALCULATE (
            SUM ( MyTable[VALUE] ),
            FILTER (
                MyTable,
                MyTable[CATEGORY] = EARLIER ( MyTable[CATEGORY] )
                    && MyTable[SUB-CATEGORY] = EARLIER ( MyTable[SUB-CATEGORY] )
            )
        )
    )

Here's the sample PBIX for your reference: https://drive.google.com/file/d/18FiQn-bj8k6jCmZDMK2xmp6Q88ttYL6s/view?usp=sharing 

[AshDil]

Hi @danextian ,

Thanks for your help. It worked for me.

Thanks,

AshDil