understanding a DAX formula

[dwhittaker1]

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

[Anonymous · Accepted solution]

Hi @dwhittaker1 

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.

[dwhittaker1]

@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 · Accepted solution]

Hi @dwhittaker1 

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

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[Anonymous · Accepted solution]

Hi @dwhittaker1 

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.