Forum Discussion
DAX help (pathetic)
Hi all,
It's newbie needing some pathetic DAX help. 😞 Edit: i have included photos, link to pbix is below.
I have the following model here with some sample data. It's a simple sales table and production table of costs.
1. What I want to do is to find the average margins associated with each customer based on the formula of
(Selling price - Ave cost price Price)/Selling Price
In my case the equivalent column headers will be
(Price each -Actual Cost)/Price each
2. Then I want to be able to have a slicer to slice systematically by years, the average margin of each customer based on average selling price and average actual cost, year by year.
Problem 1: I had errors when i tried to connect the date table to the production table of costs.
Problem 2: I created a measure called Average cost per item and I am not sure it is correct:
Here is the wetransfer link, I spent so many hours to do this small sample of data as my dataset is actually larger.
I can't sleep over this 😞 and thank you in advance for detailed explanation as I am a newbie.
Thank you so much
L
6 Replies
Hi ModelFear
Problem 1: I had errors when i tried to connect the date table to the production table of costs.
Based on the table data you have provided, I would recommend that you use the "Date" column to create the table relationship.
Problem 2: I created a measure called Average cost per item and I am not sure it is correct.
It looks like you want to group items by Item number and calculate the Average Cost Per Item for each Item number.
I have modified your code as follows:
Measure = AVERAGEX( FILTER( ALLSELECTED('ProductionTable'), 'ProductionTable'[Item number] = MAX('ProductionTable'[Item number]) ), 'ProductionTable'[Actual cost])Here is the result.
Please see attached page 2.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Dear Nono, Edit: Here are the source file and the same test file you sent in case: https://we.tl/t-l1Y8efa1DN
Thank you so much for your quick response!
I actually would like to find the average margins associated with each customer based on the standard math formula (Selling price - Ave cost price Price)/Selling Price.In my case it is:
(Price each - the ave cost calculated from Measure)/Price each
Problem: The test file you attached, in Sale table, column margin has 100% margins, as my formula using above is incorrect.Above is my aim to show customers' margins, sliced for each year. (kindly ignore the blank in the year slicer due to test data).
Thank you, I will mark as solved once I get it! This means so much to us newbies. 🙏Hi ModelFear
Create a measure:
average margins = var PriceEach = SELECTEDVALUE('SaleTable'[Price each]) RETURN IF( PriceEach <> 0, (PriceEach - 'ProductionTable'[Measure]) / PriceEach, BLANK() )I recommend that you choose to create the following visual:
Hope you found this useful!
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Dear Nono Here are the source files and the latest test file you sent for convenience: https://we.tl/t-exltVujYVw
Thank you so much for replying. Is it possible to have the average margins measure as a column in my source data that I can view under Table view sales table? A measure is hard to grasp for new bi users, who like to see what they have calculated. Having a column will allow me to make visuals easily. I understand there are concerns such as performance issues etc. Bosses would also know to click the table view to examine data or even copy table. A table visual in bi is not as easy to see or do things.
average margins = var PriceEach = SELECTEDVALUE('SaleTable'[Price each]) RETURN IF( PriceEach <> 0, (PriceEach - 'ProductionTable'[Measure]) / PriceEach, BLANK() )Thank you so much again.
L
