How to aggregrate data with multiple tagging

[GAPER]

I have a table like this

Beucase you can see one project can be tagged to multiple strategy so when we aggregrate the data it could become something like this 

I have though of using multiple ways to create the "cash flow cleaned" for exmaple dividing the sum of CF by the count of strategy. But it doesn work on the overall basis. 

May I know how do you handle that? Thanks. 

[Ritaf1983 · Accepted solution]

Hi @GAPER 
You can add an index column by project level using the linked method:
https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query

and after you'll get the table like :

you can create a simple measure for summarizing only the first row of every project:

Cf_ = CALCULATE(sum('Table'[Cf]),'Table'[Index]=1)

Result :

The pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[GAPER]

The problem is that I can't merge them into one single table because it will blow up the table size. 

[GAPER]

Works like a charm. Thanks RITA!

[Ritaf1983]

Happy to help 🙂

[GAPER]

Thanks Rita - I tried it works on the aggregration level but if i use strategy as a filter. It doesn't know how to dynamically change the index (for example project abc should also be appreaing there) Is there a way we can dynamically make the index according to the strategy?

[Ritaf1983]

Hi @GAPER 
Update the formula to :

Cf_ =

if(HASONEFILTER('Table'[Strategy]),

SUMX( SUMMARIZE('Table','Table'[Project],"Max strategy",max('Table'[Strategy]),"CF",sum('Table'[Cf])),[CF]),

CALCULATE(sum('Table'[Cf]),'Table'[Index]=1)

 )

Results:

Modified PBIX is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Ritaf1983 · Accepted solution]

Hi @GAPER 
You can add an index column by project level using the linked method:
https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query

and after you'll get the table like :

you can create a simple measure for summarizing only the first row of every project:

Cf_ = CALCULATE(sum('Table'[Cf]),'Table'[Index]=1)

Result :

The pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[GAPER]

Hi Rita - i have one follow up. Now the data doesn't sit within one single table. It's like this

Would you be able to show me how would you do it now? Thank you.

Also i am not able to find the add index in the PowerBI UI. Could you show me? Thanks

[Ritaf1983 · Accepted solution]

Hi @GAPER 
1. You need to merge the tables to get 1.
Please check this guide :
https://www.youtube.com/watch?v=m4feUpoDpVI

2. According to the index it is not a part of UI.
you have to create it. I gave the link to the guide in my first response :
https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[GAPER]

As you can see. If i pick all strategy, it's correct, if i pick X, Y, Z individually it's correct. However when I pick y&z it's giving me the wrong result. Thanks

Underlying excel

Date	Project	Strategy	CF1	CF2	Index
01/01/2024	A	x	100	-100	1
02/01/2024	A	x		-200	1
03/01/2024	A	x	200	-300	1
01/01/2024	A	y	100	-100	2
02/01/2024	A	y		-200	2
03/01/2024	A	y	200	-300	2
01/01/2024	A	z	100	-100	3
02/01/2024	A	z		-200	3
03/01/2024	A	z	200	-300	3
01/01/2024	B	x	100	-100	1
02/01/2024	B	x	200	-200	1
03/01/2024	B	x	200	-300	1
01/01/2024	B	y	100	-100	2
02/01/2024	B	y	200	-200	2
03/01/2024	B	y	200	-300	2
01/01/2024	c	Z	100	400	1
02/01/2024	c	Z	100	400	1
03/01/2024	c	Z	100	400	1

my cashflow formula. 

00.cashflow =

if(HASONEFILTER('Sheet1'[Strategy]),

SUMX( SUMMARIZE('Sheet1','Sheet1'[Project],"Max strategy",max('Sheet1'[Strategy]),"CF1",sum('Sheet1'[CF1])),[CF1]),

CALCULATE(sum('Sheet1'[CF1]),'Sheet1'[Index]=1)

 )

Thanks

[Ritaf1983]

Hi @GAPER 

I apologize, but with all the scrolling through the images, I’m having trouble understanding what you were trying to achieve and what you’ve done. The only thing I noticed is that the index doesn’t seem to be done correctly. I suggest creating a file with an example and the desired result, and including a link to it—perhaps even in a new, clean post