Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |