cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ssha
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:

ProjectJan Feb March 
 ProfitBonusProfitBonusProfitBonus
Project A20002000*0,0530003000*0,0525000 (no bonus for this month)
Project B23000 (no bonus for this month)25002500*0,0540004000*0,05

 

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

5 REPLIES 5
MFelix
Super User III
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.

 

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



ssha
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 ? 

MFelix
Super User III
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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



ssha
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

MFelix
Super User III
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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Top Solution Authors