Prevent summing constant value from another table

[jsbourni]

Hi,

I have three tables:

1. Courses (Year, semester, course, students)
2. Cost (Year, cost)
3. Year (Year)

There are multiple courses per semester and per year. There is only one flat cost per year. The table Year contains distinct values. Tables are connected through Year.

My goal is to show the flat cost for each course if there is at least one student in a course:

Course, students, cost

I tried several things, but the reasoning behind is this :

IF([students] <> BLANK(), SUM('Cout CC'[cost]), BLANK())

Obviously, it does not work. Costs keep getting summed and I'm looking to show only the yearly value.

As I mentioned, I tried with CALCULATE (which I think is necessary here for filtering), but I only

get summed values.

Thanks

[amitchandak]

@jsbourni , Create a new column in Courses 

Cost = sumx(filter(Cost, cost[Year] = Courses[Year]), cost[cost])

Then create a meausre

sumx(filter(Courses, Courses[Student] <> blank()) , Courses[Cost])

[jsbourni]

Hi,

Thanks for the suggestion. However, numbers are still adding up. Here's an example of what it's done. The flat rate for 2018-2019 is 10k. I have 5 students enrolled in the course Biology 101 that year. Since it's adding up, the costs are showing 50K.

Here's what I'm looking for. The flat rate for 2018-2019 is still 10k. Since "students" <> blank(), I want costs to show 10k. It does not matter how many students I have, it's the fact that I have at least 1 student that generates that cost.

I hope that I did not mix things up more with that explanation 😉

Thanks again, any comment will help a lot.

JS