Forum Discussion
Cost Per Formula Help
So what I need is to know how to create what I assume would have to be a "Cost Per" measure. So the example is related to students. Every student costs $200 to instruct. I have a column called "Registrations" which is the duplicated count of the Student ID. I also have the cost a student pays per registration in a column called "Course Price". Cost of instruction does not exist as a column in any of the tables. Should I create a column that says "Cost of Instruction" then just enter the amount $200 or is there a better way of doing that in Power BI?
The goal is to be able to take the total registrations and the revenue generated by taking the sum of "Course Price" less the sum of "Cost Per" to give me net revenue.
Hi JMWDBA，
Based on your description, I make a test using the sample data below.
Firstly, create new columns using formulas below.
Cost of instuctions = 200
revenue = Table2[Course Price]-Table2[Cost of instuctions]Secondly, do you want to calculate distinct registration or full registration? For the former, create a measure using DAX: Distinct registrations = DISTINCTCOUNT(Table2[Registrations]). For the latter, create a measure using DAX: full registrations = COUNT(Table2[Registrations]).
Thirdly, create card visuals to display total registrations and revenue.
If the above steps doesn’t help, please share sample data of your table and post expected result here.
Thanks,
Lydia Zhang
5 Replies
Hi JMWDBA，
Based on your description, I make a test using the sample data below.
Firstly, create new columns using formulas below.
Cost of instuctions = 200
revenue = Table2[Course Price]-Table2[Cost of instuctions]Secondly, do you want to calculate distinct registration or full registration? For the former, create a measure using DAX: Distinct registrations = DISTINCTCOUNT(Table2[Registrations]). For the latter, create a measure using DAX: full registrations = COUNT(Table2[Registrations]).
Thirdly, create card visuals to display total registrations and revenue.
If the above steps doesn’t help, please share sample data of your table and post expected result here.
Thanks,
Lydia ZhangYes, this did help immensely. Say I wanted to take it a step further. So the column named "Course" is a Concatenation of the column "Subject" and the column called "Catalog Number". If the catalog number is between 100-400 then the cost of instruction is $200 but if the catalog number is between 500-600 the cost of instruction is $300. How would i set this up so that I dont have to use a flat cost of instruction of $200?
Hi JMWDBA,
Create your column using the DAX below.
cost of instruction = IF(Table2[Catalog Number]<=400 && Table2[Catalog Number]>=100, 200, 350)Thanks,
Lydia Zhang
