Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Measure for budget on different granularity

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? 

 

Capture.JPG

 

 

THanks alot!!!

1 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:

v-kelly-msft_0-1619167403036.png

Check my .pbix file attached.

 

Best Regards,
Kelly

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

View solution in original post

5 REPLIES 5
Jihwan_Kim
Super User
Super User

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.

 

Picture1.png

 

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/

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


Go to My LinkedIn Page


Anonymous
Not applicable

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. Capture.JPG

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:

v-kelly-msft_0-1619167403036.png

Check my .pbix file attached.

 

Best Regards,
Kelly

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

amitchandak
Super User
Super User

@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
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.