Calculation between 2 Tables - Relationships

Question

Hi, I'm very new to Power Bi, so this might be an easy one for you.

I have a Table 1, which shows all customers entering a supermarket, on a given YYYYMM, and based on an irrelevant criteria, we classify them as a high spender or a low spender.

YYYYMM	Spend	Volume of Customers
201701	High	35
201701	Low	22
201702	High	28
201702	Low	24
201703	High	47
201703	Low	51
201704	High	33
201704	Low	31

I also have a Table 2, which shows all customers in the supermarket that have bought Apples. They are shown not as total number of customers but as individual entries for each customer.

YYYYMM	Spend	Good Bought	Customer Name
201701	High	Apple	John
201701	Low	Apple	Martha
201701	Low	Apple	Michael
201701	Low	Apple	Jane
201701	Low	Apple	Moody
201701	High	Apple	Paul
201701	High	Apple	Colin

I want to figure out how many customers bought Apples as a percentage of how many customers entered the Supermarket, classified as High or Low spenders.

This is the output I want:

201701	High	Apples	20%
201701	Low	Apples	35%
201702	High	Apples	15%
201702	Low	Apples	45%

I joined the two tables on YYYYMM, but when I do the following function:

Apples Pct = countrows(Table2)/sum(Table1[Volume of Customers])

I get the right aggregated percentage. But when I try inserting 'Good Bought' in Rows, my count for High and Low shows up the same as the Total value.

YYYYMM	Spend
201704	High	64
201704	Low	64
Total		64

When I try building relationships for Spend between the 2 Tables, it gives me an error for ambiguity.

Any advice?

alb · Accepted Answer

Hi Anonymous&nbsp;
I get different percentages but I'm not sure whether it is because I've understood incorrectly what you need or&nbsp; because you made a mistake in the numbers you show. In any case, try this (see it all at work in the attached file)
1. Create this measure:

MeasureCount = COUNT(Table2[Customer Name])
2. Create this other measure based on the previous one:

MeasureTotal =
DIVIDE (
    [MeasureCount];
    CALCULATE (
        SUM ( Table1[Volume of Customers] );
        FILTER (
            Table1;
            Table1[YYYYMM] = SELECTEDVALUE ( Table2[YYYYMM] )
                &amp;&amp; Table1[Spend] = SELECTEDVALUE ( Table2[Spend] )
        )
    )
)
3. Place Table2[YYYYMM], Table[Spend] and Table2[Good Bought] in values of a table visual. Make sure they all are set to 'Don't summarize'.
4. Place both [MeasureCount] and [MesureTotal] in the table visual
&nbsp;

Forum Discussion

Calculation between 2 Tables - Relationships

2 Replies

Related content

Relationship

Relationships

Relationship Issue

Creating relationships

Ignore relationship in CALCULATE