Budget (Target) Allocation, without relationship

[admin_xlsior]

Hi guys,

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

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 :

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.

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.

[CheenuSing]

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

[v-xicai]

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.

  

Best Regards,

Amy

[admin_xlsior]

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

 

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

 

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 :

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]

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

[admin_xlsior]

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,

[v-xicai · Accepted solution]

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