How to Divide/Multiply Rows in Matirx by Fixed values in a Column

[Anonymous]

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.  

[Anonymous]

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. 

[v-xiaotang · Accepted solution]

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.

[Anonymous]

Thank You! I really appreciate your assistance!

[amitchandak]

@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