Multiply column by another column

[Bfaws]

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

[mahoneypat · Accepted solution]

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

[Bfaws]

@mahoneypat 

Many thanks -with some tweaking I`ve now got the expected result.  Much appreciated.

Brendan

[Bfaws]

Hi @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

[Pragati11]

Hi @Bfaws ,

Please share some screesnots around sample data and the desired result.

Thanks,

Pragati

[Bfaws]

Hi @Pragati11 

Thanks for the response. 

Click here for link to .pbix 

The result I`m after is in the image

Instead I`m getting £1,000 for all entries even when two courses run.

Thanks,

Brendan