Split and Summarize data

[tonijj]

Hi,

Just can't find this specific issue when searching the forum. Hope anyone can help me!

Here is what I got:

Table 1 = Score table with scoring per person and per question, where the question has a unique identifier.

Table 2 = People Table with the name and department

Table 3 = Question table with the questions and some dimensions to that.

The scoring (Table 1) has two different dimension; 
-Scoring per Importance
-Scoring per Performance

What I want to achieve:
To have 1 column or measure that consolidates all the scoring for a specific Department. 

This is the wanted end state. For me it really doesnt matter if its DAX or in the Query

End result that I want to achieveEnd result that I want to achieve

[tonijj]

Hi @Shishir22 

Thanks, it doesnt quite solve my problem though with the two tables and result 🙂

1. The relationship is already there
2. I need two different tables for this solution, otherwise I wouldve gone ahead with a normal visual. However, I use Zoomcharts drilldown visuals and therefore I need to separate the datasets as described above. 

[Shishir22 · Accepted solution]

Hello @tonijj ,

If you want to bifurcate score table you can create two calculated table as -

SummaryTable1HR = 
Filter(ADDCOLUMNS('Score Table',"_Dept",RELATED('People Table'[Department])),[_Dept]="HR")

SummaryTable1Dominance = 
Filter(ADDCOLUMNS('Score Table',"_Dept",RELATED('People Table'[Department])),[_Dept]="World Dominance dept")

Please mark it as solution if it resolved your issue. Kudos are also appreciated.

Cheers,

Shishir

[Shishir22]

Hello @tonijj ,

Step 1- Give relationship between score table and people table on name.

Step 2- Create 2 table visuals and apply visual level filter of departement from people table for HR and World Dominance Dept on respective tables.

You should get required output.

Please mark it as solution if it resolves your issue. Kudos are also appreciated.

Cheers,

Shishir

[tonijj]

@Shishir22 

Thanks a bunch, that did the trick! 

I was going about this the wrong way, trying to use "summarize" or "summarizetable" then together with a filter. 

EDIT: Thankfully I didnt have more than 2 departments, otherwise this would have been...troublesome 😕 
Thanks again!