Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Error while grouping

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

1 ACCEPTED SOLUTION
v-danhe-msft
Microsoft Employee
Microsoft Employee

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:

1.PNG

You could download the pbix file to have a view.

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

13 REPLIES 13
v-danhe-msft
Microsoft Employee
Microsoft Employee

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:

1.PNG

You could download the pbix file to have a view.

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
affan
Solution Sage
Solution Sage

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

Anonymous
Not applicable

Hi @affan,

 

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

 

FilenameSourcingFileNameDateAmount
Filename: ABC 001TW_TESTS_HJG_ABC001_H1_ADJ_V1_3517_06291802415620182906044509.TXT6/30/2018 0:00-451461
Filename: ABC 001TW_TESTS_HJG_ABC001_H1_ADJ_V1_3517_06291802415620182906044509.TXT6/30/2018 0:00-70358
Filename: ABC 001TW_TESTS_HJG_ABC001_H1_V1_3469_06281802530720182806042107.TXT6/28/2018 0:00-5306.3
Filename: ABC 001TW_TESTS_HJG_ABC001_H1_V1_3469_06281802530720182806042107.TXT6/28/2018 0:00-2074.11
Filename: ABC 001TW_TESTS_HJG_ABC001_H1_V1_3469_06281802530720182806042107.TXT6/28/2018 0:00-1932.2
Filename: ABC 001TW_TESTS_HJG_ABC001_H1_V1_3469_06281802530720182806042107.TXT6/28/2018 0:00-1318.86
Filename: ABC 001TW_TESTS_HJG_ABC001_H1_V1_3469_06281802530720182806042107.TXT6/28/2018 0:00-1081.98
Filename: ABC 001TW_TESTS_HJG_ABC001_H1_V1_3469_06281802530720182806042107.TXT6/28/2018 0:00-1076.36
Filename: ABC 001TW_TESTS_HJG_ABC001_H1_V1_3469_06281802530720182806042107.TXT6/28/2018 0:00-696.01
Filename: ABC 001TW_TESTS_HJG_ABC001_H1_V1_3469_06281802530720182806042107.TXT6/28/2018 0:00-663.3
Filename: ABC 001TW_TESTS_HJG_ABC001_H1_V1_3469_06281802530720182806042107.TXT6/28/2018 0:00-659.43
Filename: ABC 001TW_TESTS_HJG_ABC001_H1_V1_3469_06281802530720182806042107.TXT6/28/2018 0:00-581.78
Filename: ABC 001TW_TESTS_HJG_ABC001_H1_V1_3469_06281802530720182806042107.TXT6/28/2018 0:00-556.68

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

 

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

image.png





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




Hi @Anonymous

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

Anonymous
Not applicable

Sure @affan,

 

This is the expected result

 

FilenameSourcingFileNameDateAmount
Filename: ABC 001TW_TESTS_HJG_ABC001_H1_ADJ_V1_3517_06291802415620182906044509.TXT6/30/2018 0:00-521819
Filename: ABC 001TW_TESTS_HJG_ABC001_H1_V1_3469_06281802530720182806042107.TXT6/28/2018 0:00-15947.01

 

Thanks

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

 

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

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
Not applicable

Hi @affan,

 

Seems to be I am missing something.

 

This is the output I am getting:

 Capture.JPG

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

Anonymous
Not applicable

Hi @affan,

 

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

 

 

Regards

@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.





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




Anonymous
Not applicable

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.

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.