cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Budget (Target) Allocation, without relationship

Hi guys,

 

I've read several sources discussion about Budget allocation which there is no relation ship to the main data. Something like this -->

image.png

 

So I have this Target table with granularity by Year, Region and Branch.

I've succeded in create a masure with it like below:

 

 - Measure no 1

Target =
VAR Region = VALUES('Location'[Region])
VAR Branch = VALUES('Location'[Branch name])

RETURN CALCULATE(
                    SUM('Target'[Expected]),
                    'Target'[Region] in Region,
                    'Target'[Branch] in Branch
                    )

 

- Measure no 2

Target value = IF(
                    Target[IsTargetYearlyValid],
                    SUMX(
VALUES('Date'[YEARKEY]),
                        CALCULATE(Target[Target],
                                    FILTER(ALL('Target'[Year]), Target[Year] = 'Date'[YEARKEY])
                        )
                    ),
                    SUMX(
                        VALUES('Date'[YEARKEY]),
                        COUNTROWS('Date') / CALCULATE(COUNTROWS('Date'), ALLEXCEPT('Date', 'Date'[YEARKEY])) *
                            CALCULATE(Target[Target],
                                        FILTER(ALL('Target'[Year]), Target[Year] = 'Date'[YEARKEY])
                            )
                    )
)

 

So basicaly, measure no 1, for calculate the target value according to filter context of my report which usually by Region and Branch, and measure no 2 to calculate the Target value in months / quarter if it the report is break down by months / quarter whereby the Target given is yearly only.

 

So I have this "beautifull" visual like this :

image.png

 

What I still have no clue, what if I want to add another table into that visual, like the Employee from Resources table which is a master / dimension table contain the list of employee. There is no employee in Target table, so there is no direct relation to that table. What it has actually, in the Resources table, there is Branch info.

 

I want to use that Branch info so that it only display resources in the same branch that display on that "above" visual.

 

Without any relationship at the moment, also there is no filter whatsoever in measures of the Target table, what I got now is all employee listed out.

 

image.png

 

Any advice on how to solve it, much appreciated. Btw how to attach a file in this message? I'd like to attach my PBIX for better sampling.

 

Thanks in advance.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Budget (Target) Allocation, without relationship

Hi  @admin_xlsior ,

 

Glad to hear the issue is resolved,  you can accept the right reply as solution, that way, other community members would benefit from the  solution.

 

Best regards

Amy Cai

6 REPLIES 6
Community Support Team
Community Support Team

Re: Budget (Target) Allocation, without relationship

Hi @admin_xlsior ,

 

I am not sure what desired result would you want, could you please share desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading. You can find OneDrive here.

 

4.png  

 

 

 

 

 

 

 

Best Regards,

Amy

 

 

CheenuSing Super Contributor
Super Contributor

Re: Budget (Target) Allocation, without relationship

Hi @admin_xlsior 

 

Please post your pbix file on Google Drive / OneDrive and share the link here.

 

Please also state what is the output you expect when you change from Year to Quarter / Month for the target values.

 

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Re: Budget (Target) Allocation, without relationship

Hi,

 

Yes, I know it is quite difficult to understand, I did create a sample PBIX until I realize I cannot attach it in here.

 

However, here it is :

https://drive.google.com/open?id=1r4gB17N_jiqL9qZt3SLkBonaElprB6W7

 

Anyway, the transition from Yearly value to Monthly is not the issue, it is already correct as they are.

What I want to achieve or the main issue is later when you see my PBIX, I have this table visual which contain :

- Year

- Region

- Branch

- Target Value

image.png 

Then I have a Resource table, it is a list of Worker, which it has the info of their branch. (Resource branch)

image.png 

So, I want to add this Resources to the 1st table visualization, but it needs to match the branch code of the 1st visual, so it only display the Resources belong to the branch in the respected row.

 

The matching is something like this :

image.png

 

So table visualization no 1, for row no 1, because it says the branch code is 109, it will only listed Resources (from table no 2) with Resource branch 109, which is Julia Funderburk. The target value may remain as it is.

For row no 2, Branch is 110, the Resource only 2, Jack Robinson and Julia Anderson.

For row no 3, Branch is 112, the Resource are 2 also, with colored green. 

and so on.

 

What I have now, all worker in Resources are listed in my Table visual.

 

CheenuSing Super Contributor
Super Contributor

Re: Budget (Target) Allocation, without relationship

Hi @admin_xlsior ,

 

Looks like an issue of relationship in the model.  I did the following

 

1. Deleted the relationship

      TimesheetTransaction --> Location

 

2. Changed the Resource --> Target to  Resource-->Location

 

Now when I select any row in table 1 , the values in table 2 relect only of resources belonging to that branch code.

 

Check it out.

 

Cheers

 

CheenuSing

 

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Re: Budget (Target) Allocation, without relationship

Hi,

 

Many thanks for your help.

Actually I cannot delete the relationship between Timesheet since it is my FACT Table. However, I did make the relationship again between Location and Resources but make it inactive (to avoid ambiguity)

 

In the weirdest way, somehow it is works. 

 

Thanks again. Really appreciate this.

 

regards,

 

Community Support Team
Community Support Team

Re: Budget (Target) Allocation, without relationship

Hi  @admin_xlsior ,

 

Glad to hear the issue is resolved,  you can accept the right reply as solution, that way, other community members would benefit from the  solution.

 

Best regards

Amy Cai

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 109 members 1,492 guests
Please welcome our newest community members: