Counting distinct values of the same dimension in multiple columns

[MilanRao06]

Hi Community,

I have been trying to solve this use-case for a long, long time and now seeking your help. 

I have the below columns in my data. 

Expectation1	Exp1_Score	Expectation2	Exp2_Score	Expectation3	Exp3_Score
AAA	5	BBB	6	CCC	5
AAA	6	CCC	4
BBB	4	AAA	3	CCC	2
CCC	7	AAA	4	BBB	6
AAA	7	BBB	5

I want add a scatter chart with the x-axis being % of each unique expectation and the y-axis being the average of each of the expectation. In summary the below should be the summarized data and the visual. 

Expectation	Count	Ratio	Average Score
AAA	5	42%	5
BBB	4	33%	5.25
CCC	3	25%	4.5

I ofcourse want to slice and dice using the various dimensions.

Please help me crack this.

Regards,

Milan

[v-shex-msft · Accepted solution]

Hi @MilanRao06,

You can refer to below steps to achieve your requirement.

1. Create new table  to store the merged table.

Formula:

Merged Table = 
FILTER(DISTINCT(UNION(SELECTCOLUMNS(Sheet1,"Expectation",[Expectation1],"Score",[Exp1_Score]),SELECTCOLUMNS(Sheet1,"Expectation",[Expectation2],"Score",[Exp2_Score]),SELECTCOLUMNS(Sheet1,"Expectation",[Expectation3],"Score",[Exp3_Score]))),[Score]<>BLANK()||[Expectation]<>BLANK())

2. Summarize merged table.

Formula:

Result Table = 
var total= SUMX(ALL('Merged Table'),[Score]) 
return
SUMMARIZE('Merged Table','Merged Table'[Expectation],"Count",COUNT('Merged Table'[Expectation]),"Radio",SUMX(FILTER(ALL('Merged Table'),[Expectation]=EARLIER('Merged Table'[Expectation])),[Score])/total,"Average",AVERAGE('Merged Table'[Score]))

3. Use result table to create scatter chart.

Expectation to Detail and Legend fields, Radio to x-Axis , Average to Y-Axis, Count to Size field.

Regards,

Xiaoxin Sheng

[v-shex-msft · Accepted solution]

Hi @MilanRao06,

You can refer to below steps to achieve your requirement.

1. Create new table  to store the merged table.

Formula:

Merged Table = 
FILTER(DISTINCT(UNION(SELECTCOLUMNS(Sheet1,"Expectation",[Expectation1],"Score",[Exp1_Score]),SELECTCOLUMNS(Sheet1,"Expectation",[Expectation2],"Score",[Exp2_Score]),SELECTCOLUMNS(Sheet1,"Expectation",[Expectation3],"Score",[Exp3_Score]))),[Score]<>BLANK()||[Expectation]<>BLANK())

2. Summarize merged table.

Formula:

Result Table = 
var total= SUMX(ALL('Merged Table'),[Score]) 
return
SUMMARIZE('Merged Table','Merged Table'[Expectation],"Count",COUNT('Merged Table'[Expectation]),"Radio",SUMX(FILTER(ALL('Merged Table'),[Expectation]=EARLIER('Merged Table'[Expectation])),[Score])/total,"Average",AVERAGE('Merged Table'[Score]))

3. Use result table to create scatter chart.

Expectation to Detail and Legend fields, Radio to x-Axis , Average to Y-Axis, Count to Size field.

Regards,

Xiaoxin Sheng

[MilanRao06]

Thanks for the response @v-shex-msft.

Your solution did help me get the desired visual, albeit a static one. I wanted a visual that responds to the various filters in the dashboard. 

For example, in my example, each row of data has dimensions like Customer Name, Business Unit, Geo etc. The scatter plot should render based on the filters chosen.

Thanks,

Milan

[MilanRao06 · Accepted solution]

I solved this. 

Below is the table of Merged Table.

MergedTable = FILTER(UNION(SELECTCOLUMNS('Sheet1',"Unique Id",'Sheet1'[Unique id],"Expectation",'Sheet1'[Exp 1 Master Code 1],"Score",'Sheet1'[EXP1 RAT1]),SELECTCOLUMNS('Sheet1',"Unique Id",'Sheet1'[Unique id],"Expectation",'Sheet1'[Exp 2 Master Code 1],"Score",'Sheet1'[EXP2 RAT2]),SELECTCOLUMNS('Sheet1',"Unique Id",'Sheet1'[Unique id],"Expectation",'SHeet1'[Exp 3 Master Code 1],"Score",'Sheet1'[EXP3 RAT3])),[Score]<>BLANK()&&[Expectation]<>BLANK())

I then created a 1:M relationship between the Unique Id columns of MergedTable and Sheet1.

Then plotted the scatter-plot with Details = Expectation, Legend = Expectation, Y-axis = Avg of Score and X-axis = Count of Score. Since this table is linked to the master Sheet1, I am able to slice & dice based on the dimensions in Sheet1. 

Thanks again for the solution @v-shex-msft.

Regards,

Milan