Count multiple exact same values from different columns and from different tables

[Anonymous]

Hi, i would really apreciate some help with my issue, as i´ve been trying different methods for hours but i cant achieve what i want.

I have a main table with different types of tests: F.1,F2.,F.3,F.4,F.5,F.6,F.7. Each test has different data associated to, for example, number of times they were made (showing them in count format as each test done generates a row).

Meanwhile, there are another 7 tabs (Excel file), for each type of test. Each tab contains a column with each month (12 columns then) . Everytime a test is started, a row is generated with another classification of test: X or E. This happens for each tab for each type of tests (F.1,F.2....etc). In this tab (table), is not displayed the name of each test (F1,F2...), as the tab name already says which one is it.

Example:

Tab/table F.1

Ene Feb 

X     X

E     E

X     E

X     X

E     E

E     X

My objective is to display in a bar chart how many X and E tests happened for each type of test (F.1,F.2...etc)

I would love you forever if you could help me achieve this.

Thank you in advance.

[BA_Pete]

Hi @Anonymous ,

When you import your tabs into Power Query, I'd add a new custom column to each of your F.X tables which just contains the F.X value for that table. I'd then unpivot the month columns to 'normalise' the structure of each of these tables, then I'd append them all on top of each other.

I'd then create a single F_dimension table that just holds the unique F.X values . I'd relate this to your main table and to your appended F.X table so you can now filter both of them by F.X value using your dimension table.

You should be able to then write measures based on either table and will have filter control over both via the dimension table.

*NB* I've had to make some assumptions about your data to provide this rough walkthrough. If you could share an example of your Excel file then I'd happily take another look and write a proper solution based on your actual data.

Pete

[Anonymous]

Hi Pete, thanks for yoour reply. Unfortunately i cannot share any file as it´s confidential info from the company im working with.

Question: how can i create a custom column which gets the X/E values from all months? Any formula?