cancel
Showing results for
Did you mean:
Frequent Visitor

## Using Month Columns of a Matrix in a Measure

Hello,

I have a specific problem. I have a matrix visual with months a columns (jan, feb, march,..). I have different projects as rows and the costs, revenue and profit as values. What I need to do is calculate the bonus each employee gets for the first three months of a project (example: project starts on Jan 1st, so they get a bonus for Jan, Feb and March). The bonus is something easy like 5% * profit of the project.

What I want to do is write a measure inside the matrix visual to check whether the employee is allowed to get a bonus and if yes calculate 5% * profit (of the project for the month) and use it as a value inside the matrix. Since I have the months as columns, I want to use them in my measure, but unfortunately I cannot access the months in my measure.

I need one measure, which will automatically apply for all the month columns in the matrix. How can I solve this ?

EXAMPLE:

 Project Jan Feb March Profit Bonus Profit Bonus Profit Bonus Project A 2000 2000*0,05 3000 3000*0,05 2500 0 (no bonus for this month) Project B 2300 0 (no bonus for this month) 2500 2500*0,05 4000 4000*0,05

P.S.: Every data comes from a different dataset so the relationships are very complicated

5 REPLIES 5
Super User III

Hi @ssha

This is possible to do however without knowing the way your model is setup is difficult to give you the correct answer.

If the information is sensitive please share it trough private message.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Frequent Visitor

Hi MFelix,

sadly I cannot show my model since it would be against the company policy. Explaining it would be also very difficult since we have 10 different tables which are connected to each other.
What I am getting from you is that depending on the model and the relationships it might be possible ?

Super User III

Hi @ssha ,

Yes is possible just a few questions is your profit in a table with Project , month, profit or is it a column for each month like project, profot month one, profit mobth two,

Profit month 4?

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Frequent Visitor

Hi MFelix,

each data comes from another table. The profit comes from one table, the costs come from another table, the projects are in another table, even the months come from another table. It is truly a very comlicated model, I myself am not a fan of it but this is how it is.

Kind regards

Super User III

Hi @ssha,

I understand what you are saying but without any information about the model is literally impossible to give you the correct answer since depending on the way the columns are present in your model, the links between the tables I can only guess what is the answer.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Announcements