cancel
Showing results for
Search instead 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

1 REPLY 1
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

Helpful resources

Announcements

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

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.

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors