Forum Discussion
How to Divide/Multiply Rows in Matirx by Fixed values in a Column
Hello and thank you in advance for reading this post. I have a matrix that I am creating and I need to divide a row I created in the Matrix with a fixed column of numbers. This is a simple task in Excel, I will give an example:
| Event | Count of type | Goal | Maximum points | Percentage of Goal |
| Outreach | 50 | 40 | 10 | |
| Relationship Building | 19 | 20 | 10 | |
| Sponsored Events | 20 | 20 | 5 | |
| Sales Finalized | 45 | 40 | 30 |
Here I have different events which I then made a count column to separate the events into their own type. The [Goal] column and [Maximum points] column are fixed values (meaning I chose those numbers to work with), and [Percentage of Goal] which is my desired output.
I want to divide [count of type]/[goal] and have the results go in the [percentage of Goal] column. Lastly, once I get the [percentage of Goal] column, I want to multiply the maximum points as these are my weights and have a new column to represent that as well. I know how to divide/multiply rows in Excel and have the desired columns output in their corresponding rows, but I don't know how to approach this in PowerBI.
Hi Anonymous
Try this,
Calculated column code:
Percentage of Goal = VAR _CountOfType = CALCULATE ( COUNT ( Table1[Type] ), FILTER ( ALL ( Table1 ), Table1[Event] = Table2[Event] ) ) VAR _Goal = Table2[Goal] RETURN DIVIDE ( _CountOfType, _Goal )Column = Table2[Maximum points]*Table2[Percentage of Goal]Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Anonymous , based on what I got If you have an event available as a dimension. You can create a table using enter data and join it with the event table
How to Enter Data and Edit it: https://youtu.be/5nE7YGT72kU
or create a measure
Switch(Max(table[event]) ,
"Outreach",40 ,
"Relationship Building" ,20 ,
"Sponsored Events" , 20,
"Sales Finalized", 40)
and use that
Thank you for reaching out to me, perhaps I didn't explain myself properly.
I created two tables: the first table (table 1) has the information about the events. Let me give you an example of how (table 1) looks. This is a small example of the large dataset I have.
Created By
Event Type Date Created ... John Smith Outreach prospecting Jan 1 ... Jane Air Outreach prospecting Jan 3 ... John Smith Relationship Building Relations Jan 14 ... John Smith Relationship Building Relations ... ... Huckleberry Finn Finalized sales Engagement ... ... Most of the data is irrelevant but I'm primarily focused on the event, name of the person, and the type.
When I create the matrix I grouped [type] together as a count so it would show in the matrix I posted above.
Now here is the second table (table 2)
Event Goal Maximum points Outreach 40 10 Relationship Building 20 10 Sponsored Events 20 5 Sales Finalized 40 30 This is a table I created, and I made a relation between them using the [Event] column in both (table 1) and (table 2). In (table 1) there are names of individuals there. I was able put the [goals] and [maximum points] on the matrix as shown in the previous post.
I used a filter to look at each Individuals performance in each event. I want to be able to do the following:
Event Count of type Goal Maximum points Percentage of Goal Outreach 50 40 10 125% Relationship Building 19 20 10 95% Sponsored Events 20 20 5 100% Sales Finalized 45 40 30 112.50% For example John Smith completed 50 outreached for the month of January and the goal he has to meet is 40, this meant he 125% of his goal.
I want to divide [count of type] by [Goal] and have the corresponding solution in a column how its shown in Red.
Hi Anonymous
Try this,
Calculated column code:
Percentage of Goal = VAR _CountOfType = CALCULATE ( COUNT ( Table1[Type] ), FILTER ( ALL ( Table1 ), Table1[Event] = Table2[Event] ) ) VAR _Goal = Table2[Goal] RETURN DIVIDE ( _CountOfType, _Goal )Column = Table2[Maximum points]*Table2[Percentage of Goal]Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
