Forum Discussion
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.
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
5 Replies
Unpivot your data into a usable format
ID Material Test Result ID_materialapplication quantity_Attribut_Value
2 material 1 1,00 % Test 1 13 2 material 1 1,00 % Test 2 200 2 material 1 1,00 % Test 3 56-75 2 material 1 1,00 % Test 4 17 2 material 1 1,00 % Test 5 56,4 2 material 1 1,00 % Test 6 86,6 2 material 1 1,00 % X-axis 26,05 2 material 1 1,00 % Y-axis -0,22 2 material 1 1,00 % Z-axis -0,54 2 material 1 1,00 % delta X to nearest standard 0,27 2 material 1 1,00 % delta Y to nearest standard -0,25 2 material 1 1,00 % delta Z to nearest standard 0,01 2 material 1 1,00 % Test 13 0,1 2 material 1 1,00 % Test 16 7,1 2 material 1 1,00 % Test 17 cracks 2 material 1 1,00 % Test 18 cracks 2 material 1 1,00 % Test 19 G0 2 material 1 1,00 % Test 20 4,6 4 material 2 1,00 % Test 1 13 4 material 2 1,00 % Test 2 200 4 material 2 1,00 % Test 3 70-88 4 material 2 1,00 % Test 4 15,9 4 material 2 1,00 % Test 5 54,8 4 material 2 1,00 % Test 6 86,2 4 material 2 1,00 % X-axis 26,17 4 material 2 1,00 % Y-axis -0,33 4 material 2 1,00 % Z-axis -0,6 4 material 2 1,00 % delta X to nearest standard 0,39 4 material 2 1,00 % delta Y to nearest standard -0,36 4 material 2 1,00 % delta Z to nearest standard -0,05 4 material 2 1,00 % Test 13 0,13 4 material 2 1,00 % Test 16 7,3 4 material 2 1,00 % Test 17 small cracks 4 material 2 1,00 % Test 18 small cracks 4 material 2 1,00 % Test 19 G2 4 material 2 1,00 % Test 20 7,41 6 material 3 1,00 % Test 1 13 6 material 3 1,00 % Test 2 200 6 material 3 1,00 % Test 3 64-78 6 material 3 1,00 % Test 4 14,8 6 material 3 1,00 % Test 5 53 6 material 3 1,00 % Test 6 84,2 6 material 3 1,00 % X-axis 26,24 6 material 3 1,00 % Y-axis -0,42 6 material 3 1,00 % Z-axis -0,59 6 material 3 1,00 % delta X to nearest standard 0,46 6 material 3 1,00 % delta Y to nearest standard -0,45 6 material 3 1,00 % delta Z to nearest standard -0,04 6 material 3 1,00 % Test 13 0,08 6 material 3 1,00 % Test 16 7,6 6 material 3 1,00 % Test 17 cracks 6 material 3 1,00 % Test 18 cracks 6 material 3 1,00 % Test 19 G0 6 material 3 1,00 % Test 20 4,59 9 material 4 1,00 % Test 1 13 9 material 4 1,00 % Test 2 200 9 material 4 1,00 % Test 3 68-84 9 material 4 1,00 % Test 4 14,9 9 material 4 1,00 % Test 5 53 9 material 4 1,00 % Test 6 85 9 material 4 1,00 % X-axis 25,98 9 material 4 1,00 % Y-axis -0,3 9 material 4 1,00 % Z-axis -0,45 9 material 4 1,00 % delta X to nearest standard 0,32 9 material 4 1,00 % delta Y to nearest standard -0,32 9 material 4 1,00 % delta Z to nearest standard -0,02 9 material 4 1,00 % Test 13 0 9 material 4 1,00 % Test 16 7,3 9 material 4 1,00 % Test 17 cracks 9 material 4 1,00 % Test 18 cracks 9 material 4 1,00 % Test 19 G1 9 material 4 1,00 % Test 20 3,22 11 material 5 1,00 % Test 1 13 11 material 5 1,00 % Test 2 200 11 material 5 1,00 % Test 3 67-76 11 material 5 1,00 % Test 4 13,7 11 material 5 1,00 % Test 5 50,6 11 material 5 1,00 % Test 6 84,6 11 material 5 1,00 % X-axis 26,65 11 material 5 1,00 % Y-axis -0,97 11 material 5 1,00 % Z-axis -0,35 11 material 5 1,00 % delta X to nearest standard 0,99 11 material 5 1,00 % delta Y to nearest standard -0,99 11 material 5 1,00 % delta Z to nearest standard 0,08 11 material 5 1,00 % Test 13 0,09 11 material 5 1,00 % Test 16 7,7 11 material 5 1,00 % Test 17 cracks 11 material 5 1,00 % Test 18 cracks 11 material 5 1,00 % Test 19 G0 11 material 5 1,00 % Test 20 4,47 3 material 1 3,00 % Test 1 13 3 material 1 3,00 % Test 2 200 3 material 1 3,00 % Test 3 79-88 3 material 1 3,00 % Test 4 10,4 3 material 1 3,00 % Test 5 45,7 3 material 1 3,00 % Test 6 83 3 material 1 3,00 % X-axis 26,24 3 material 1 3,00 % Y-axis -0,45 3 material 1 3,00 % Z-axis -0,57 3 material 1 3,00 % delta X to nearest standard 0,46 3 material 1 3,00 % delta Y to nearest standard -0,48 3 material 1 3,00 % delta Z to nearest standard -0,02 3 material 1 3,00 % Test 13 0,03 3 material 1 3,00 % Test 16 7,7 3 material 1 3,00 % Test 17 small cracks 3 material 1 3,00 % Test 18 small cracks 3 material 1 3,00 % Test 19 G1 3 material 1 3,00 % Test 20 5,43 5 material 2 3,00 % Test 1 13 5 material 2 3,00 % Test 2 200 5 material 2 3,00 % Test 3 67-80 5 material 2 3,00 % Test 4 9,3 5 material 2 3,00 % Test 5 42,5 5 material 2 3,00 % Test 6 82,4 5 material 2 3,00 % X-axis 26,36 5 material 2 3,00 % Y-axis -0,55 5 material 2 3,00 % Z-axis -0,57 5 material 2 3,00 % delta X to nearest standard 0,58 5 material 2 3,00 % delta Y to nearest standard -0,58 5 material 2 3,00 % delta Z to nearest standard -0,02 5 material 2 3,00 % Test 13 0,04 5 material 2 3,00 % Test 16 7,4 5 material 2 3,00 % Test 17 cracks 5 material 2 3,00 % Test 18 cracks 5 material 2 3,00 % Test 19 G0 5 material 2 3,00 % Test 20 4,44 7 material 3 3,00 % Test 1 13 7 material 3 3,00 % Test 2 200 7 material 3 3,00 % Test 3 78-85 7 material 3 3,00 % Test 4 9,5 7 material 3 3,00 % Test 5 43,2 7 material 3 3,00 % Test 6 81,3 7 material 3 3,00 % X-axis 26,44 7 material 3 3,00 % Y-axis -0,63 7 material 3 3,00 % Z-axis -0,58 7 material 3 3,00 % delta X to nearest standard 0,66 7 material 3 3,00 % delta Y to nearest standard -0,66 7 material 3 3,00 % delta Z to nearest standard -0,03 7 material 3 3,00 % Test 13 0,07 7 material 3 3,00 % Test 16 7,3 7 material 3 3,00 % Test 17 cracks 7 material 3 3,00 % Test 18 cracks 7 material 3 3,00 % Test 19 G0 7 material 3 3,00 % Test 20 5,16 10 material 4 3,00 % Test 1 13 10 material 4 3,00 % Test 2 200 10 material 4 3,00 % Test 3 56-76 10 material 4 3,00 % Test 4 9,9 10 material 4 3,00 % Test 5 43,7 10 material 4 3,00 % Test 6 82,2 10 material 4 3,00 % X-axis 26,17 10 material 4 3,00 % Y-axis -0,49 10 material 4 3,00 % Z-axis -0,45 10 material 4 3,00 % delta X to nearest standard 0,51 10 material 4 3,00 % delta Y to nearest standard -0,51 10 material 4 3,00 % delta Z to nearest standard -0,02 10 material 4 3,00 % Test 13 -0,05 10 material 4 3,00 % Test 16 7,9 10 material 4 3,00 % Test 17 cracks 10 material 4 3,00 % Test 18 cracks 10 material 4 3,00 % Test 19 G0 10 material 4 3,00 % Test 20 2,87 12 material 5 3,00 % Test 1 13 12 material 5 3,00 % Test 2 200 12 material 5 3,00 % Test 3 67-90 12 material 5 3,00 % Test 4 9,7 12 material 5 3,00 % Test 5 43,1 12 material 5 3,00 % Test 6 81,6 12 material 5 3,00 % X-axis 28,22 12 material 5 3,00 % Y-axis -2,51 12 material 5 3,00 % Z-axis -0,29 12 material 5 3,00 % delta X to nearest standard 2,56 12 material 5 3,00 % delta Y to nearest standard -2,53 12 material 5 3,00 % delta Z to nearest standard 0,14 12 material 5 3,00 % Test 13 0,4 12 material 5 3,00 % Test 16 7,3 12 material 5 3,00 % Test 17 cracks 12 material 5 3,00 % Test 18 cracks 12 material 5 3,00 % Test 19 G1 12 material 5 3,00 % Test 20 3,36 1 Standard 1 0,00 % Test 1 13 1 Standard 1 0,00 % Test 2 200 1 Standard 1 0,00 % Test 3 63-79 1 Standard 1 0,00 % Test 4 17,5 1 Standard 1 0,00 % Test 5 57,5 1 Standard 1 0,00 % Test 6 86,4 1 Standard 1 0,00 % X-axis 25,78 1 Standard 1 0,00 % Y-axis 0,03 1 Standard 1 0,00 % Z-axis -0,55 1 Standard 1 0,00 % Test 16 7 1 Standard 1 0,00 % Test 17 cracks 1 Standard 1 0,00 % Test 18 cracks 1 Standard 1 0,00 % Test 19 G1 1 Standard 1 0,00 % Test 20 4,84 8 Standard 2 0,00 % Test 1 13 8 Standard 2 0,00 % Test 2 200 8 Standard 2 0,00 % Test 3 65-85 8 Standard 2 0,00 % Test 4 17,5 8 Standard 2 0,00 % Test 5 56,9 8 Standard 2 0,00 % Test 6 86,2 8 Standard 2 0,00 % X-axis 25,66 8 Standard 2 0,00 % Y-axis 0,02 8 Standard 2 0,00 % Z-axis -0,43 8 Standard 2 0,00 % delta X to nearest standard N/A 8 Standard 2 0,00 % Test 16 7,6 8 Standard 2 0,00 % Test 17 cracks 8 Standard 2 0,00 % Test 18 cracks 8 Standard 2 0,00 % Test 19 G0 8 Standard 2 0,00 % Test 20 5,55
current file:
https://drive.google.com/file/d/1xV0HxMPvUKvYnsuraEUzrz84J4QPtTI2/view?usp=sharing
the question is how can I calculate the standard value minus the values for each test individually?Trying to make sense of your data.
- are you comparing the materials against the standards for each of the quantities?
- what happened to the non-numeric values? ("crack" etc)
I have now tried to update the task definition and implement a template in the original request.
Regarding your questions:
- In this version I removed all non-numeric values in order to reduce complexity.
- Unfortunately, the name “Standard” is actually a bit misleading -> As you can now read above, the difference to a the name Standard 1/2/... is the name of the blank value that is needed to determine the confidence range and, if necessary, to be able to represent the influence of a 1%/3% input amount of a material.
