Forum Discussion
How to Divide/Multiply Rows in Matirx by Fixed values in a Column
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.
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.
Thank You! I really appreciate your assistance!