Forum Discussion

ModelFear's avatar
ModelFear
Frequent Visitor
2 years ago

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 a...
Need Help
ModelFear's avatar
ModelFear
Frequent Visitor
2 years ago

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. 🙏
 
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      ModelFear
      Frequent Visitor
      2 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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.