Group and Sum Row by Row

[Anonymous]

Hello,

Please see below,

I want to group “KEY” column once I group same key ,In a new column I want to display total quantity and total sales price.

I have used Group by and I am getting below error,

test = GROUPBY('2018 RAW Data','2018  RAW Data'[Key],'2018  RAW Data'[Comment],"Invoice Total",SUMX('2018  RAW Data','2018 PY RAW Data'[Quantity]))

“Function 'GROUPBY' scalar expressions have to be Aggregation functions over CurrentGroup(). The expression of each Aggregation has to be either a constant or directly reference the columns in CurrentGroup().”

Can any one suggest me some way?

Source
Key	Quantity	Sales Price
ACB-123-State	1	10
ACB-123-State	2	20
ACB-456-Provience	3	15
ACB-456-Provience	4	5
Expected Result
Key	Quantity Total	Sales Price Total
ACB-123-State	3	30
ACB-456-Provience	7	20

[Anonymous · Accepted solution]

give this a try:

Table = 
ADDCOLUMNS(
    SUMMARIZECOLUMNS(Table1[Key]),
    "Total Qty", CALCULATE( SUM( Table1[Quantity])),
    "Total Sales Price", CALCULATE( SUM( Table1[Sales Price]))
)

[Anonymous]

Thank you so much Nick.

[Anonymous]

@Anonymous no problem. And just so you know, you don't have to physically create this table if you dont want to. You can use anywhere where you a table is allowed, in say, SUMX. 

[Anonymous]

Perfect & Thank you so much Nick.

[Anonymous]

Hi Nick,

Thank you so much for your suggestion.

When i tried your suggestions i am getting below error,

"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.".

Thanks

---

@Anonymous wrote:

give this a try:

Table = 
ADDCOLUMNS(
    SUMMARIZECOLUMNS(Table1[Key]),
    "Total Qty", CALCULATE( SUM( Table1[Quantity])),
    "Total Sales Price", CALCULATE( SUM( Table1[Sales Price]))
)

---

[Cmcmahan]

Are you creating this as a new table, or as a new measure?  This should be a new table

[Anonymous]

Hi,

i do not want new table i want two new columns.

Thanks

---

@Cmcmahan wrote:

Are you creating this as a new table, or as a new measure?  This should be a new table

---

[Anonymous]

Hi 

@Cmcmahan suggestion is correct. You need to have a new table, not additional columns. The expected result you showed us is a summarized table, with a different grain (one row per each key).

[Anonymous]

Thanks for your suggestion.

You both are right and it is giving me the right numbers.

Thanks Again.