Combining data into one bar chart

[Danielwood]

Hi,

I'm quite new to Power BI so this may be a stupid question.

I have a few tables that ideally I want to link to show one bar graph of data.

Table one has a list of 25 unique values in column A, then in columns B-E it has 4 groups (eg Fruit, Veg, Grain, Other). For every entry in column A, it will have a Y or N if its part of the 4 groups. 

Table 2 then has some other unique references in column A, eg a product code. Then in columns B onwards it has those 25 unique values from Table 1. For each product, it has a Y or N if its part of the 25 unique values.

What I then want to do is show for the 4 groups, how many products have a Y against a unique value (in Table 2)  that then also has a Y in that group (in Table 1).

I've probably explained that terribly, but hopefully you can help. 

Thanks

Dan

[jgeddes · Accepted solution]

Sure thing.

The basic idea is that we need to unpivot both tables and then build a relationship between them.

To unpivot the tables go into Power Query via the Transform data icon

On Table1 select the Product column and then from the Transform portion of the ribbon select Unpivot Other Columns. 

I renamed the resulting "Attribute" column as "Category"

Do the same for Table2 selecting the "Code" column and unpivoting the other columns

In this case I rename the "Attribute" column to "Product" to match the column name in Table1

Close and Apply these changes to go back into the BI screen.

You can now set a Many to Many relationship between the two tables using the product columns. It will filter in both directions.

The measure I used was;

Both Y =

CALCULATE(

    COUNT(Table1[Category]),

    FILTER(Table2,Table2[Value]="Y"),

    FILTER(Table1,Table1[Value]="Y")

)

Hope this helps.

[jgeddes]

If I understand correctly, your data looks something like;

with the second table continuing to include up to SKU 25.

And your desired result is something like

Correct?

[Danielwood]

Hi,

Yeah that's the kind of thing I'm after. Are you able to explain how you did that please?

The source data is in a Microsoft access database if that helps.