cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ARB17 Regular Visitor
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):

Community.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

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
v-ljerr-msft Super Contributor
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. Smiley Happy

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

View solution in original post

1 REPLY 1
v-ljerr-msft Super Contributor
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. Smiley Happy

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

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

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

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

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.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Users Online
Currently online: 429 members 4,326 guests
Please welcome our newest community members: