Issue Averaging in Visuals Using buckets from multiple tables

[Anonymous]

The next thing I might try is to merge my little join table into a new table that can contain all the columns that I need.  The problem is that I am working with large amounts of data with direct SharePoint connections and the software crashes a lot...

[v-juanli-msft]

Hi @Anonymous

First please let me know these:

1.which table is large? “Teams hours data”?

2.Is the relationship among these tables set as “single” or “both” for “cross filter direction”?

3.do you use columns“project1” and “hrsmin” from the table “Teams hours data” in a “table” visual to form the table visual “aggregatetable by project”?

4.Is your final requirement as follows :

Drag “category” column from “GlobalPM” table to the table visual “aggregatetable by project”, then the “hrsmin” aggregated by the “GlobalPM”.

Right?

Best Regards

Maggie

[Anonymous]

Hi  Maggie, thank you for the reply, 

1. The TT-A-HoursByMonth table is ~14,000 rows and fairly wide ~20 variables and the SAPM-Merge table is a join of the SAPM-TargetDates and the SAPM-Actual dates table.  The merge table is ~286 rows but it is very wide, more like 35-40 variables.

2. The relationships are mixed, I have both directions from the main tables to the join table (TT main table and SAPM-Merge table) and to the main table for sub tables.  See image I attached.  I was also forced to use the Many:Many relationship to be able to join use a join table to connect the SAPM-Merge table to the TT-Hours table.

3.  My big issue was that the project management team uses all specific project names and the leadership in my department made their buckets in their data table using groupings or different naming conventions.  The join table matches these up but there are duplicates in both the TT Project NAmes column and the SAPM Project names column.  I need to be able to make meaningful buckets from the SAPM table and use the hours in the TT table to do my calculations

4. My final requirement that I am after is to use the calculated bucket I made with all the dates and things in the SAPM (Global Project Table) to categorize the hour calculations in my TT table.  For example I want to see average FTE estimates by project bucket rather than by person or project name.  To do this I will have to make an aggregate somehow otherwise the visuals use the smallest unit for the averaging rather than the category I chose in my matrix visual.

 

[Anonymous · Accepted solution]

I think I may have figured it out. I was able to merge the SAPM-Project Name to the TT table from my join table more formally rather than using it as a bridge.  Then I rearraged my relationships a little so I could do the suggestions here:

https://community.powerbi.com/t5/Desktop/Merge-multiple-tables-using-DAX/td-p/372054

https://dax.guide/related/

https://www.sqlbi.com/articles/lookup-multiple-values-in-dax/

The Related() function now worked 🙂