Forum Discussion

jsbourni's avatar
jsbourni
Helper II
3 years ago

Prevent summing constant value from another table

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
 
 
Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    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's avatar
      jsbourni
      Helper II
      3 years ago

      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