Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
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.


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



Anonymous
Not applicable

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 ? 

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?


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



Anonymous
Not applicable

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

Hi @Anonymous,

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.