Forum Discussion
Helper IIIMultiply column by another column
Hi,
I was hoping someone could help. I have two tables - one is my data table that has rows showing attendees on various L&D courses. The courses themselves can be run multiple times and each time a course is run it is given a new course ID. This helps me to identify how many times a specific course was run. For example "Sales Training Course" is a course(column title [COURSE], and each time it is run its given a new ID like "Sales Training Course 01.01.2018" - column title [Course ID]
I also have a seperate lookup table that contains a single entry for each course type [COURSE] and a column showing how much its cost to run this course (column [Course Cost]).
The result I`m looking is essesntially how much its costs to run a specific course if there was only one run or multiple run. So if the sales course was run 10 times in a month at a cost £1,000 per course then the measure will show £10,000 using the [Course ID] to identify how many times [Course] was run.
Thanks in advance for any suggestions.
Brendan
You can use an expression like this to get the total cost of all course that includes the # of repeat courses. This assumes you have a many:1 relationship between your table with courseIDs to the table with the cost of each course.
Total Cost = SUMX ( VALUES ( CourseIDTable[CourseName] ), CALCULATE ( DISTINCTCOUNT ( CourseIDTable[CourseID] ) ) * RELATED ( Courses[CourseCost] ) )If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
5 Replies
- mahoneypat
Microsoft Employee
You can use an expression like this to get the total cost of all course that includes the # of repeat courses. This assumes you have a many:1 relationship between your table with courseIDs to the table with the cost of each course.
Total Cost = SUMX ( VALUES ( CourseIDTable[CourseName] ), CALCULATE ( DISTINCTCOUNT ( CourseIDTable[CourseID] ) ) * RELATED ( Courses[CourseCost] ) )If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
BfawsHi mahoneypat
Thanks for the suggestion.
I`ve tried to apply this to my report but when I get to the related part of the function i`m unable to select any options. Apologies - I`m quite new to DAX and BI. There is a link in my previous post if that helps.
Thanks,
Brendan
- Bfaws
Many thanks -with some tweaking I`ve now got the expected result. Much appreciated.
Brendan
