Grouping on a data from Analysis services

[Anonymous]

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

[affan · Accepted solution]

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]

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

[RMDNA]

Hi @Anonymous,

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

[Anonymous]

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]

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]

Hi @Baskar,

Sum is also retruing the same result.

How can I create a column?

Regards

[v-lili6-msft · Accepted solution]

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

[affan · Accepted solution]

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]

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