Forum Discussion
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
- MFelix
Super User
Hi Anonymous
This is possible to do however without knowing the way your model is setup is difficult to give you the correct answer.
Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.
If the information is sensitive please share it trough private message.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 ?- MFelix
Hi Anonymous ,
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?
