Input multiple hierarchy data in identical line chart in Power bi

[Anonymous]

Hi All,

I have a dimension table (building list) and a fact table (Rent history per building) in data model (below):

i can show per building rent history in line chart (x-axis is Year-month, like below) with filter by different hierarchy range from District (highest level), Sub-district to building (lowest level). 

Question: I want to add district level rent average into identical graph so when building(s) was / were selected in slicer, corresponding district rent average history will be shown as well. I can perform in seperate graph like below but is more appropriate to shown in identical graph for easy comparison:

I have try below:

1. Add district average per Year-month to Rent fact table and try show both building rent and distract rent in line chart, but cannot figure out dax (averageifs in excel) and line chart value field can only show >1 value when legend field is empty, which might not be appropriate for me as there're several hundred buildings so it's easier for user to easier notice.

2. Create 2 sepeate query (avg rent per building & per distract) and union them in seperate table as data source. But I still fail to locate a key to filter buildings with corresponding district average when lowest level (building) was selected, i.e. if I choose building A / A & B (same district), no district average was shown.

Appreciate for yours help on this and I can clarify anything if I didn't express clearly in above. Thanks.

[Anonymous]

Hi @Anonymous 

Has your problem been solved ? If it has been solved, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regard

Community Support Team _ Ailsa Tao

[Anonymous · Accepted solution]

Hi @parry2k ,

Thanks for the reply.

I finallly fix it by create a snapshot of all required data at seperate table by DAX and twick on backend data model.

But thanks a lot for your help and comments on above. ^^

Regards,

TWMY1014

[parry2k]

@Anonymous check this post, a similar question was answered in this post.Solved: Re: Line chart with Legend and additional measure - Page 2 - Microsoft Power BI Community as matter of fact, in the next few days I'm about to do a video on it, if interested, do subscribe to my YouTube channel (link below) 

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.