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

Grouping on a data from Analysis services

Hi Team,

 

Need your help, I am trying to download a report from Analysis Services and data is huge thus exceeding download limits.

 

I am trying to group the data, however sum is not comming as expected.

 

Please suggest.

 

I am attaching data for reference:

 

Source data:

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

 

Output required:

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
2 ACCEPTED SOLUTIONS
affan
Solution Sage
Solution Sage

Hi @Anonymous,

 

Can you try using the following measure 

 

TotalAmount = CALCULATE(SUM(CreditOutputSummary[Amount]),ALLEXCEPT(CreditOutputSummary,CreditOutputSummary[SourcingFileName]))

If this helped you, please mark this post as an accepted solution and like to give KUDOS .

 

Regards,

Affan

 

View solution in original post

hi, @Anonymous

     If you connect to Analysis Services by live connection, it can only create the report level measure. it can't create any

columns or tables.

and when drag fields into visual and set the aggregate type of field amount to Sum, the visual will automatically

group aggregated data.

28.PNG

Best Regards,

Lin

 

Community Support Team _ Lin
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

8 REPLIES 8
Anonymous
Not applicable

Hi Team,

 

Your support is required as I got stuck while grouping the below data.

Source data:

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

 

Output required:

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 for your support

Abhijit

Hi @Anonymous,

 

In Query Editor, use Group By with the following settings:

 

3.PNG

Anonymous
Not applicable

Hi @RMDNA,

 

Thanks for the advice, as I am fetching this data from an Analysis Services and thats why I am trying to group the data via measures.

 

 

Baskar
Resident Rockstar
Resident Rockstar

Just try sum(amount) ,
why you are trying the Allexcept ?

Do you want to create measure or column ?

If measure just use sum(amount)

Anonymous
Not applicable

Hi @Baskar,

 

Sum is also retruing the same result.

How can I create a column?

 

Regards

hi, @Anonymous

     If you connect to Analysis Services by live connection, it can only create the report level measure. it can't create any

columns or tables.

and when drag fields into visual and set the aggregate type of field amount to Sum, the visual will automatically

group aggregated data.

28.PNG

Best Regards,

Lin

 

Community Support Team _ Lin
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

Hi @Anonymous,

 

Can you try using the following measure 

 

TotalAmount = CALCULATE(SUM(CreditOutputSummary[Amount]),ALLEXCEPT(CreditOutputSummary,CreditOutputSummary[SourcingFileName]))

If this helped you, please mark this post as an accepted solution and like to give KUDOS .

 

Regards,

Affan

 

Anonymous
Not applicable

Hi @affan,

 

Thanks for your support, previously I also used the same logic and got the below result:

 

 

 

Here is the data at transactional level:

 

Transaction wiseTransaction wiseAfter measureAfter measure

I need total amount as -521819.

 

Thanks

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.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.