Ragged Hierachy Weightage Average (SUMPRODUCT)

[Anonymous]

Hello Hi,

I'm trying to achieve weightage average sumproduct calculations based on my data. The expected output should look like this.

I have created parent-child hierarchy for my data as per below:

1) Matrix View

2) Data

I have tried several guides from the internet but unfortunately failed to generate the output required.

I hope someone can assist me with this.

Best Regards.

Attached herewith is the pbix files.

Pbix Files 

[parry2k · Accepted solution]

@Anonymous I believe this YT video of mine is the answer to your question. Let's explore the underrated ISINSCOPE DAX function - What is it? How we can use it? - YouTube

and part 1 of this video Many Functions Same Result - Which one to use and Why? ALLSELECTED REMOVEFILTERS and VALUES - YouTube

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort 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.

[Anonymous]

Hi Mr Parry,

Thanks for your reply and video. I did manage to get the column of the weightage. I have another question. Below is my data. 

As you can see, the item starting with "CONC" is considered an activity and I have actual progress of that activity. For example, the first CONC is 100% and the data is queried from another table. How can I show the 100% but on the parent row (2.4.8.2 - Fabrication Main Deck) is the calculated value of SUMPRODUCT(activity, act_weightage). 

Is there any way to tell Power BI to show value on last-child based on table (since its ragged hierarchy) and calculated parent.