cancel
Showing results for
Did you mean:
Highlighted
Helper II

## 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
Highlighted
Microsoft

## 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

Highlighted
Microsoft

## 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

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### July 2020 Community Highlights

Learn about the exciting things that happened in July.

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors