Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

understanding a DAX formula

I am having a heard time Understanding the formula below. I am trying to place the following measure in a table
 
Price Impact - Sales - CY vs PY $ =
IF(
    [Price per Unit - PY $]=BLANK(),
        BLANK(),
        SUMX(
            FILTER(
                ALL(  FactSalesInvoiceLine[InvoiceItemID],FactSalesInvoiceLine[InvoiceMonth] ),
                [Price per Unit - PY $] > 0
                ),
            CALCULATE([Unit Quantity - CY]*[Price per Unit - CY vs PY $])
        )
)
 
 
I placed the measure in the table below as shown below. The measure returns a summarized value on each row,   how can i adjust the measure to look at each row and provide a calculation for each row instead summarizing the value in each row. for example row 1 unit quanity of 2100 * price per unit of .17 should return a value of 357
 

 

 
 
 
 
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous 

     

    I simply restored your data, all but "Price Impact - Sales - CY vs PY $" are the source data.

     

    I created a measure as follows.

    Price Impact - Sales - CY vs PY $ = 
IF(
    MAX([Price per Unit -PY $]) = BLANK(),
        BLANK(),
        SUMX(
            FILTER(
                FactSalesInvoiceLine,
                [Price per Unit -PY $] > 0
                ),
            [Unit Quantity - CY] * [Price per Unit - CY vs PY $]
        )
)

     

    Result:

     

    If this is not your desired outcome, please show your desired outcome in excel form.

     

    Best Regards,
    Yulia Xu

     

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

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous 

     

    [Price per Unit - PY $] is a measure?

     

    If so, please try removing MAX. I don't have your source data, so in my test the field is column need to use MAX.

     

    Best Regards,
    Yulia Xu

     

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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous 

     

    I simply restored your data, all but "Price Impact - Sales - CY vs PY $" are the source data.

     

    I created a measure as follows.

    Price Impact - Sales - CY vs PY $ = 
IF(
    MAX([Price per Unit -PY $]) = BLANK(),
        BLANK(),
        SUMX(
            FILTER(
                FactSalesInvoiceLine,
                [Price per Unit -PY $] > 0
                ),
            [Unit Quantity - CY] * [Price per Unit - CY vs PY $]
        )
)

     

    Result:

     

    If this is not your desired outcome, please show your desired outcome in excel form.

     

    Best Regards,
    Yulia Xu

     

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Anonymous 

     

    thanks for the response, I get  a message that says The MAX function only accepts a column reference as the argument  number 1

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Anonymous 

       

      [Price per Unit - PY $] is a measure?

       

      If so, please try removing MAX. I don't have your source data, so in my test the field is column need to use MAX.

       

      Best Regards,
      Yulia Xu

       

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