Error while grouping

[Anonymous]

Hi Team,

Need your support, I am getting an error

"Query (3, 5) Calculation error in measure 'CreditOutputSummary'[1cols]: 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()."

While typing below code

Filename = GROUPBY(CreditOutputSummary,CreditOutputSummary[DataSourcingFileName],"Files",SUM(CreditOutputSummary[CreditAmount]))

Thanks for your support

Regards

Abhijit Mishra

[v-danhe-msft · Accepted solution]

Hi @Anonymous,

Based on my test, you could refer to below formula:

Filename2 = var a=SUMMARIZE('CreditOutputSummary','CreditOutputSummary'[SourcingFileName],"t",SUM(CreditOutputSummary[Amount]))
return SUMX(a,[t])

Result:

You could download the pbix file to have a view.

Regards,

Daniel He

[affan]

Please share your pbix file so that I can help you on this

[Anonymous]

Hi @affan,

Wont be able to share due to access issue, I can share a raw format.

Filename	SourcingFileName	Date	Amount
Filename: ABC 001	TW_TESTS_HJG_ABC001_H1_ADJ_V1_3517_06291802415620182906044509.TXT	6/30/2018 0:00	-451461
Filename: ABC 001	TW_TESTS_HJG_ABC001_H1_ADJ_V1_3517_06291802415620182906044509.TXT	6/30/2018 0:00	-70358
Filename: ABC 001	TW_TESTS_HJG_ABC001_H1_V1_3469_06281802530720182806042107.TXT	6/28/2018 0:00	-5306.3
Filename: ABC 001	TW_TESTS_HJG_ABC001_H1_V1_3469_06281802530720182806042107.TXT	6/28/2018 0:00	-2074.11
Filename: ABC 001	TW_TESTS_HJG_ABC001_H1_V1_3469_06281802530720182806042107.TXT	6/28/2018 0:00	-1932.2
Filename: ABC 001	TW_TESTS_HJG_ABC001_H1_V1_3469_06281802530720182806042107.TXT	6/28/2018 0:00	-1318.86
Filename: ABC 001	TW_TESTS_HJG_ABC001_H1_V1_3469_06281802530720182806042107.TXT	6/28/2018 0:00	-1081.98
Filename: ABC 001	TW_TESTS_HJG_ABC001_H1_V1_3469_06281802530720182806042107.TXT	6/28/2018 0:00	-1076.36
Filename: ABC 001	TW_TESTS_HJG_ABC001_H1_V1_3469_06281802530720182806042107.TXT	6/28/2018 0:00	-696.01
Filename: ABC 001	TW_TESTS_HJG_ABC001_H1_V1_3469_06281802530720182806042107.TXT	6/28/2018 0:00	-663.3
Filename: ABC 001	TW_TESTS_HJG_ABC001_H1_V1_3469_06281802530720182806042107.TXT	6/28/2018 0:00	-659.43
Filename: ABC 001	TW_TESTS_HJG_ABC001_H1_V1_3469_06281802530720182806042107.TXT	6/28/2018 0:00	-581.78
Filename: ABC 001	TW_TESTS_HJG_ABC001_H1_V1_3469_06281802530720182806042107.TXT	6/28/2018 0:00	-556.68

[PattemManohar]

@Anonymous Please try this, by adding a "New Table"

FileAmountOut = SUMMARIZECOLUMNS(FileAmount[Filename],FileAmount[SourcingFileName],FileAmount[Date],"Amount",SUM(FileAmount[Amount]))

[affan]

Hi @Anonymous

Can you also share the expected result! What you are trying to acheive.

[Anonymous]

Sure @affan,

This is the expected result

Filename	SourcingFileName	Date	Amount
Filename: ABC 001	TW_TESTS_HJG_ABC001_H1_ADJ_V1_3517_06291802415620182906044509.TXT	6/30/2018 0:00	-521819
Filename: ABC 001	TW_TESTS_HJG_ABC001_H1_V1_3469_06281802530720182806042107.TXT	6/28/2018 0:00	-15947.01

Thanks

[affan]

@Anonymousfor this result you don't need any measure or calculated column.

See the below I have created from the data you shared.

You can download the PBIX file for your reference.

https://www.dropbox.com/s/stjui4thb296zc1/Error%20while%20grouping.pbix?dl=0

Please mark this post as an accepted solution if this helped you.

Regards,

Affan

[Anonymous]

Hi @affan,

Seems to be I am missing something.

This is the output I am getting:

 

[affan]

Have you tried the pbix file I have shared? If yes, can you share the data underlying your visual

[Anonymous]

Hi @affan,

I am fetching this data from an Analysis Services and thats why I am trying to group the data via measures.

Regards

[PattemManohar]

@Anonymous Could you please try using “Group By” option in “Power Query Editor”. Also, make sure you use all fields in group by that you want in the result set (FileName, SourceFileName, Date) and sum the amount field. It will resolve your issue.

[Anonymous]

Hi @PattemManohar,

I am fetching this data from an Analysis Services and thats why I am trying to group the data via measures, hence couldnt use query editor.

[v-danhe-msft]

Hi @Anonymous,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Regards,

Daniel He