Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Calculations from related information between 3 ta...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

ARB17

Helper II

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-19-2018
04:59 AM

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

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

v-ljerr-msft

Microsoft

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-22-2018
12:34 AM

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

v-ljerr-msft

Microsoft

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-22-2018
12:34 AM

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

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

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

Top Solution Authors

User | Count |
---|---|

385 | |

130 | |

95 | |

95 | |

88 |

Top Kudoed Authors

User | Count |
---|---|

505 | |

187 | |

186 | |

143 | |

117 |