Visualise differences with selectable reference
hello big power bi community, I am currently struggling with the following problem and hope that someone might have an idea how to solve it: Background: Different materials with different concentrations were tested in a laboratory. A lot of different tests were carried out, some of which provided numerical and some text-based results. Each material was tested at two concentrations 1% and 3%. In order to define the general deviation of the process and the confidence range of the values, standards "1/2/3...." with 0% concentration was carryed out as well. Report vision in total: 1 Page) Welcome page -> Linked buttons to the individual pages with explanations of the pages and their use cases 2 pages) On the first report page the user should get an overview of the big data set. Contents: Radar chart with all tests and filters by tests + Button to switch to Matrix/Table view 3 pages) On the third report page, values should be compared to a self-defined referenz. The user should use the slicer to select which experiment he would like to see the respective deviations for. Contents: Visualizations for each test for each experiment/material show the respective absolute and % deviation from the previously defined referenz. The deviation between the standards (that are actuall trials in the dataset) should be given as a confidence interval (can be solved in excel if necessary) 4 pages) A material should first be selected on the 4th report page. that filters the entire page. The aim is to show how increasing the amount used influences the test result. Contents: Visualizations for each test for just one material showing the two use concentrations 1% and 3%. (optional)= also the deviation of the two concentrations to each other 5 Page) - (optional because it is very complicated) The best match for a defined standard or set default values should be searched for on the 5th report page. The goal is to find a material that has the same properties, regardless of concentration. Best Match= lowest deviation in all tests To do this, you would have to aggregate (summarize) the results of all tests into one numerical value. Difficulty: Test results must be weighted differently and be adjustable with a slicer. My Problem ist marked in bold: * On page 3 I can't calculate the deviation from a referenz selected dynamically with the Slicer * On page 4 I can't figure out how to calculate the deviation between the two concentrations for each material * I think page 5 is so complex that I see it as optional Notes on the data set: [real data set includes approx. 100 materials and approx. 30 tests ] Results are not only numerical but can also be text (here I am not yet sure how to solve this - perhaps numerical assignment?) raw data: ID 1 2 3 4 5 6 7 8 9 10 11 12 material Standard 1 material 1 material 1 material 2 material 2 material 3 material 3 Standard 2 material 4 material 4 material 5 material 5 application quantity 0% 1% 3% 1% 3% 1% 3% 0% 1% 3% 1% 3% Test 1 13 13 13 13 13 13 13 13 13 13 13 13 Test 2 200 200 200 200 200 200 200 200 200 200 200 200 Test 3 63-79 56-75 79-88 70-88 67-80 64-78 78-85 65-85 68-84 56-76 67-76 67-90 Test 4 17,5 17 10,4 15,9 9,3 14,8 9,5 17,5 14,9 9,9 13,7 9,7 Test 5 58 56 46 55 43 53 43 57 53 44 51 43 Test 6 86,4 86,6 83 86,2 82,4 84,2 81,3 86,2 85 82,2 84,6 81,6 X-axis 25,78 26,05 26,24 26,17 26,36 26,24 26,44 25,66 25,98 26,17 26,65 28,22 Y-axis 0,03 -0,22 -0,45 -0,33 -0,55 -0,42 -0,63 0,02 -0,3 -0,49 -0,97 -2,51 Z-axis -0,55 -0,54 -0,57 -0,6 -0,57 -0,59 -0,58 -0,43 -0,45 -0,45 -0,35 -0,29 delta X to nearest standard 0,27 0,46 0,39 0,58 0,46 0,66 0,32 0,51 0,99 2,56 delta Y to nearest standard -0,25 -0,48 -0,36 -0,58 -0,45 -0,66 -0,32 -0,51 -0,99 -2,53 delta Z to nearest standard 0,01 -0,02 -0,05 -0,02 -0,04 -0,03 -0,02 -0,02 0,08 0,14 Test 13 0,1 0,03 0,13 0,04 0,08 0,07 0 -0,05 0,09 0,4 Test 16 7 7,1 7,7 7,3 7,4 7,6 7,3 7,6 7,3 7,9 7,7 7,3 Test 17 cracks cracks small cracks small cracks cracks cracks cracks cracks cracks cracks cracks cracks Test 18 cracks cracks small cracks small cracks cracks cracks cracks cracks cracks cracks cracks cracks Test 19 G1 G0 G1 G2 G0 G0 G0 G0 G1 G0 G0 G1 Test 20 4,8 4,6 5,4 7,4 4,4 4,6 5,2 5,6 3,2 2,9 4,5 3,4 Template/Test data transformed: Download Many thanks in advance to everyone how give some feedback 🙂 BR Phill