Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

How to aggregrate data with multiple tagging

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. 

 

Need Help

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Works like a charm. Thanks RITA!

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    2 years ago

    Hi Anonymous 
    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Ritaf1983
      Super User
      2 years ago

      Hi Anonymous 
      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

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