Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
Proud to be a Super User!
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
Proud to be a Super User!
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
Proud to be a Super User!
User | Count |
---|---|
99 | |
86 | |
78 | |
75 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |