Calculating averages and multiplying across multiple tables

[Anonymous]

Hi finally posting this message since all of my searching has yielded no results. Hope I can get some help!

I am working with two tables:

Table 1 (Capabilities):

Capability; Capability Category; 2019; 2020; 2021

1; A; 0%; 50%; 50%

2; A; 100%; 0%; 0%

3; B; 10%; 40%; 50%

4; B; 50%; 20%; 30%

5; C; 100%; 0%; 0%

Table 2 (Values)

Value; Value Category; Capability Category; Amount

abc; 0.1; A; $100

def; 0.1; B; $80

ghi; 0.2; B; $50

jkl; 0.3; C; $200

mno; 0.1;C;$20

As you can see the tables have a many to many relationship on Capability Category.

What I have done is unpivoted the years columns on the Capabilities column to get something like this:

Capability; Capability Category; Year; Value

1; A; 2019; 50%

1; A; 2020; 50%

and so on..

Okay, now what I need:

1. A measure to average the Value column so that I can say "30% is being delivered in 2019 for all categories" but adding Categories to this should allow me to say 20% of category 1 is being deliveres in 2019 and 50% for category 2 and so on.

2. A way to multiply amount from the Values table to the Categories table such that it factors the average per year. So for example:

Capability Category; Amount

A; $100

Becomes:

Capability Category; Year; Amount

A; 2019; $0

A; 2020; $50

A; 2021; $50

Have tried creating a bridge table for Categories, creating measures that average for a category to no avail.

[v-piga-msft]

Hi @Anonymous ,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best  Regards,

Cherry

[v-piga-msft]

Hi @Anonymous ,

---

Okay, now what I need:

1. A measure to average the Value column so that I can say "30% is being delivered in 2019 for all categories" but adding Categories to this should allow me to say 20% of category 1 is being deliveres in 2019 and 50% for category 2 and so on.

2. A way to multiply amount from the Values table to the Categories table such that it factors the average per year. So for example:

Capability Category; Amount

A; $100

I have still confused about your logic of your two requirements.

If it is convenient, coudl you explain it in more details.

You could create a bridge table with category column like below then you could create the relationships for your data model.

Do you want to get the output below for your first requirement?

If it is, please try the measure below.

Measure 2 =
CALCULATE (
    AVERAGE ( 'Capabilities'[Value] ),
    ALLEXCEPT (
        Capabilities,
        Capabilities[Year],
        Capabilities[Capability Category]
    )
)

Best  Regards,

Cherry