Forum Discussion
Looking for trends in unrelated related data
What constitutes "wrong" fur color?
In your sample above, the Fur color is a numerical value. If you want to mark this as either right or wrong, then that is already one piece of analysis that you need to undertake in a separate step.
For example, you could create a column in the data that shows "fur color status", for which the value can be wrong or right or whatever. To arrive at the value, you may want to employ a formula that evaluates some of the other properties of that puppy. Maybe skin colors below 10 should have fur colors over 100 and if the fur color is not over 100, it is classified as wrong. Or something like that. An IF function should do the trick. With that function in place, you can then classify the data by the fur color status.
- Do Puppies from Litter 2 have a higher-than-average rate of wrong fur color?
Create a pivot table that shows the average rate for each fur color status in the columns, the litter numbers in the rows.
fur color status Litter correct wrong 1 80% 20% 2 75% 25% 3 95% 5% 4 19% 89% overall 67% 35% - Do all Puppy 4s have a higher-than-average rate of wrong fur color?
use a similar table, but with puppy numbers in the rows.
- Do all Site 21s have a higher-than-average rate of wrong fur color?
use a similar table, but with site numbers in the rows.
- If a Puppy's fur color is wrong, does that mean there's a greater-than-average likelihood that their fur length is wrong?
- If a Puppy's fur color is wrong, does that mean there's a greater-than-average likelihood that their eye color is wrong?
The first three bullets were simple calculations. These last two are about probability and go deeper into the realm of statistical analysis. Again, you will first need to build the structures (helper columns) required to classify by fur length status and eye color status.
If you load the data into the Power Pivot data model, you can add the helper columns there. You can then also use the powerful statistical DAX functions to create measures and then surface the results in pivot tables.
I'm not sure whether your question is more about understanding how Excel works, or what math to use to calculate a value with several variables, or if you need help with the whole concept of statistical analysis.
Neither of these can be explained in a single forum question, because they each have their own learning curves.
jamesson_kaupanger It looks like the tables all have the same structure. In order to analyze across these tables, use Power Query to Append the queries into one. Load this into the Data Model.
I don't understand how puppy 2 can be the same for different litters. Biologically that is not possible, but if you use this as a metaphor only, sure, use the puppy number/code/name as the unique ID.
You can now build pivot tables and use slicers and/or filters to look at all qualities/properties across all tables.
If you need help building these pivot tables, I would need to know what questions the pivot tables/charts should answer.
