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
