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

    • ModelFear's avatar
      ModelFear
      Frequent Visitor
      2 years ago

      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