Forum Discussion
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:
| 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 |
Output required:
| 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 |
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
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.
Best Regards,
Lin
8 Replies
Hi Team,
Your support is required as I got stuck while grouping the below data.
Source data:
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 Output required:
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 for your support
Abhijit
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
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 wiseAfter measure
I need total amount as -521819.
Thanks
Just try sum(amount) ,
why you are trying the Allexcept ?Do you want to create measure or column ?
If measure just use sum(amount)
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.
Best Regards,
Lin
