Calculating averages and multiplying across multiple tables

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