Calculations from related information between 3 tables

[Anonymous]

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

[v-ljerr-msft · Accepted solution]

Hi @Anonymous,

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