Measure for budget on different granularity

[Anonymous]

Hi All, 

I have 3 tables in total and I've created a graph showing the actuals by "team hierarchy". the issue here is that i get the budget just on a team level and would like to include that in my graph as a line. So therefore, when i click on slicer by team, it should show me the budget but if i go down to the person level, the budget should show blank. How best do i configure this? 

THanks alot!!!

[Jihwan_Kim]

Hi, @Anonymous 

Please check the below picture and the sample pbix file's link down below, whether it is what you are looking for.

All measures are in the sample pbix file, and the steps are numbered in front of each measure.

https://www.dropbox.com/s/obe3spxl18z3yzh/evko.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

[Anonymous]

Thanks Jihwan for the response but team A should show a budget of 1200 not 2300. I do not need the budget to be allocated among team members. 

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ,

Create below column in team hierarchy:

Budget Amount = 
var _budget=CALCULATE(SUM('Budget'[Amount]),FILTER(ALL(Budget),'Budget'[Team]=EARLIER('Team'[Team])))
var _count=CALCULATE(DISTINCTCOUNT(Team[Name]),FILTER(ALL(Team),'Team'[Team]=EARLIER(Team[Team])))
Return
DIVIDE(_budget,_count)

And you will see:

Check my .pbix file attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@Anonymous , Try to merge Table 1 and Table 3 using the name in power query and use Table 2 as a dimension and also take some of the budget from there

Try https://radacad.com/append-vs-merge-in-power-bi-and-power-query

[Anonymous]

Could you create a trial formula for me? because i still cannot get it to work. I've merge the query now but im in doubt about the last part about creating the measure