DAX help (pathetic)

[ModelFear]

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:

Average Cost Per Item =

VAR _CurrentItem = SELECTEDVALUE(ProductionTable[Item number])

RETURN

    AVERAGEX(

        FILTER(ProductionTable, ProductionTable[Item number] = _CurrentItem),

        ProductionTable[Actual cost]

    )

Here is the wetransfer link, I spent so many hours to do this small sample of data as my dataset is actually larger.

https://we.tl/t-HqPogA7JnH

I can't sleep over this 😞 and thank you in advance for detailed explanation as I am a newbie.

Thank you so much

L

[ModelFear]

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. 🙏

[v-nuoc-msft]

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.

[ModelFear]

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

[v-nuoc-msft]

Hi @ModelFear 

In response to your question: "Is it possible to have the average margin measurement as a column in the source data that I can view under the table view sales table?"

You can certainly include average profit margin measurements as a column in the source data.

Create a column.

Column = 
    AVERAGEX(
    FILTER(
        ALLSELECTED('ProductionTable'[Item name]),
        'ProductionTable'[Item number] = EARLIER('ProductionTable'[Item number])
    ),
    'ProductionTable'[Actual cost]
)

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ModelFear]

Dear Nono,  Edit: Hi Nono, i hope to get your kind help. I still have no answer. Thanks much.🤔 
Here are the source files and the latest test file you sent for convenience: https://we.tl/t-exltVujYVw
Thank you for the quick response.
Erm, what I meant is margins (profit), and not the actual cost as provided above.

Instead of a measure for margins, I would like a margin column, in fact table :

Currently, the margins in the attached file is incorrect 100% because look at the formula:

Pl pardon my ignorance on Dax, this is the steepest learning curve for new users 😞

Thanks

L

[v-nuoc-msft]

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.