Need help in creating measure

[Hassanmoussa]

Hi All,

I have the following 2 tables. Table 1:

Class	Semester	Student	Program
ABCD	100	1003030	IT
ABCD	100	1002020	BBA
EFGH	100	1002020	BBA

Table 2: ( all calculated columns are calculated in DAX and not query)

Class	Semester	Teacher	Salary in Term (calculated column through related())	Number of sections (Calculated column through calculate() and filter())	Enrolled Students (Calculated column through calculate() and Filter from Table1)	Cost per Student (calculated column = salary / number of sections / number of students)
EFGH	100	Hamda	20000	2	1	10000
ABCD	100	Hamda	20000	2	2	5000
ABCD	100	Ali	10000	1	2	5000

What I am trying to get to is:

Teacher	Cost to Program
Ali     IT Program     BBA Program	5000 5000
Hamda     IT Program     BBA Program	5000 15000

Is creating a measure for this task possible? What is the formula and Where Do I create this measure? BTW, I have more tables in my models but the above should simplifies my requirement. Below are details:

[v-yiruan-msft]

Hi @Hassanmoussa,

Please check if the solution provided by @Anonymous (using the matrix visual ) can help you get the result you want . If no , please provide which tables the fields used in your expected result are from ? And if there is any measure or calculated column applied , please also provide the relevant formula . If these fields are from different tables, please provide the join field between 2 tables. Thank you .

Best Regards

Rena

[Hassanmoussa]

Hello,

Many thanks @v-yiruan-msft and @Anonymous. Unforunetly it is not what I am looking for.

I am trying to create a measure not a visual that will count (or sum) the faculty cost for each student enrolled in a class. Let me know if what I say makes sense.

As an example, see below cost per student? I want to aggregate that. The real sum will be 100 Million+. But the below table is showing 13 million only because these amounts are defined in the course instructors table. 

I guess a formula would = sum(count(student id) * cost per student). But this is not working.

[v-yiruan-msft]

Hi @Hassanmoussa，

From the information you provided earlier, cost per student is a calculated column whose calculation formula is (salary / number of sections / number of students). Could you please provide which columns from which tables are involved? I'm not sure if there will be a context change when put the previous calculated column into new measure...

In addition, how do you calculate faculty cost in real life?Could you please provide your PBIX file if it is convenient?

Best Regards

Rena

[Anonymous · Accepted solution]

Hi you can create a matrix to achieve this.

rows = teacher & program

values = cost

as in your example you can disable subtotals like this: