cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Calculations from related information between 3 tables

Hello,

I am having problems with the following issues:

The data model is as follows (simplified):

The idea is the following:

SALESMAN contains the different Sales people.

SALES has the invoices, relating each Salesman and its Client with an OFFICE (level 1). It is monthly data.

OFFICE has the hierarchy of offices (level 2 > level 1), its description, etc.

AVERAGES has the expected sales average per level 2 office (applies to all level 1 offices in that level 2).

With that date, the way of knowing to which OFFICE reports each SALESMAN is by analyzing the month's SALES. The problem is how to reach, in a Power BI measure, from the SALESMAN to its expected AVERAGE:

The logic I have is:

SALESMAN > DISTINCT(OFFICE Lvl 1) in SALES > DISTINCT(OFFICE Lvl 2) in OFFICES > AVERAGES (per Lvl 2).

I have managed to get the average if I imput manually just one Lvl 1 OFFICE, it gets the AVERAGE as expected, using LOOKUPVALUE to get its Lvl 2, and with that the average. But when I try to do a measure that automatically gets the AVERAGE of the DISTINCT(Lvl 2) based on the DISTINCT(Lvl 1) present in SALES, it gives an error.

So, the main problem is either working with arrays of data, or doing the DISTINCT of a DISTINCT. It is done on the by-date filtered SALES, which I have, but I am not able to get this expected AVERAGE by SALESMAN.

Furthermore, if I have several SALESMAN filtered, the measure should the the AVERAGE of the AVERAGES. Any help on how could I get this, which DAX functions to use? Maybe LOOKUPVALUE is not the most suiting one.

Regards and thank you,

Antonio

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

## Re: Calculations from related information between 3 tables

Hi @ARB17,

Could you try using the formula below to create a new measure to see if it works in your scenario.

```Measure =
AVERAGEX (
SALESMAN,
CALCULATE (
AVERAGEX ( DISTINCT ( OFFICE ), CALCULATE ( SUM ( AVERAGES[Value] ) ) )
)
)
```

Note: Make sure you have set Cross Filter Direction to Both for all the relationships in your model.

Regards

Super Contributor

## Re: Calculations from related information between 3 tables

Hi @ARB17,

Could you try using the formula below to create a new measure to see if it works in your scenario.

```Measure =
AVERAGEX (
SALESMAN,
CALCULATE (
AVERAGEX ( DISTINCT ( OFFICE ), CALCULATE ( SUM ( AVERAGES[Value] ) ) )
)
)
```

Note: Make sure you have set Cross Filter Direction to Both for all the relationships in your model.

Regards

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 429 members 4,326 guests
Recent signins: