Building a forecast budget

[GohrLouis]

Hello Everyone,

First of all, i'm using powerbi since a years and a half, this is one of my biggest project.

I want to do some forecast budgeting with the human ressources by adding some hypothesis using parameters or by dropping a file with hypothesis.

I have a couples of tables:
My first one is a dimension table "Segmentation" where all may category of what i paid are
My second, a calendar
My third, is what happened everymonth when we paid people, connected to the first and the second one.

Then for budgeting:

I have a table called "detail" where i get all my staff with the annual theorical pay, on the last day of the previous year.

I have another table "table-Budget" with the forecast calendar of the year connected to the calendar and to the "detail", with the 12 month of 2024.

I have a calculated table, equal to "table-Budget" then i put lot of measure inside of it, like, Salary =calculate(sum(theorical pay])/13,'table-Budget'[date]) and couples of other.
Then i want to have these measure connected to the "segmentation" table so i created another table to put all of these measure inside 1 columns using UNION and SELECTCOLUMNS.

It works good until i wanted to do some calculation depending on the salary of a members, and it allway take the total. So i want my measure or something else to be effective on only the members and then aggregate it, also if it works, when i want to filter on the members i want to only see him inside a table if i used the list of the staff as a segment.

It seems that the measure cannot hold the salary of each indivduals and just doing some aggregations.

I will try to put some PBIX tomorrow to show

Thanks for all the help

[GohrLouis]

Hello,

Thanks @v-jingzhan-msft , but does not seems to work, or maybe i misunderstood.

PBIX 

Here is my PBIX, i don't know if it worked.

My goal is in the sheet : "BUDGET VS REAL" to be able to filtered on the "Matricule"

And in "Page3", the measure to work in global not if only filtered. Because i want to see it globally and personnally.

The main table used are:

• Détail
• Date for all the measure
• Regroupementtest : to transform measure in one column for relation
• Segmentation: to connect real and budget category

The rest are used for measure purposes.

Kind regards.

Louis


Here i 

[v-jingzhan-msft]

Hi @GohrLouis 

You can try adding a dimension table for Staff and connect it to other tables like the Salary table and the Budget table. Then add the Staff table's member column into a slicer to filter other visuals by staff. 

For the measures, usually we can use measures in visuals directly thus they can be evaluated dynamically according to the filters in its context. If you populate a calculated table with measures, this makes the measures undynamic. And if you want to see some dynamic results in visuals according to filters, you may have to create some additional measures to achieve the goal. 

Best Regards,
Jing