Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· OpenAIAi2 WildChat-4.8M (AI2)

I have a table with columns date, gp, delivery, Disc, action. How to write Dax…

  1. User

    I have a table with columns date, gp, delivery, Disc, action. How to write Dax expression for calculated column if required to find (gp - delivery)/Disc for all transactions for each action?

  2. Assistant

    To calculate the expression (gp - delivery)/Disc for all transactions for each action in a table with the given columns, you can create a calculated column using the DAX expression:

    CalculatedColumn = ([gp] - [delivery]) / [Disc]
    

    This formula subtracts the value in the "delivery" column from the value in the "gp" column and divides the result by the value in the "Disc" column for each row.

    Make sure to replace "CalculatedColumn" with the desired name for the calculated column in your table.

  3. User

    Some rows have the same value in column action. So, we need to have the same calculated value in all rows with the same action

  4. Assistant

    To have the same calculated value in all rows with the same action, you can use the DAX function called CALCULATE in combination with the SUMX function to sum the calculated values for each action. Here's an example of how you can write the DAX expression for the calculated column:

    CalculatedColumn = 
    VAR ActionTotal =
        SUMX (
            FILTER (
                'YourTable',
                'YourTable'[action] = EARLIER ( 'YourTable'[action] )
            ),
            ('YourTable'[gp] - 'YourTable'[delivery]) / 'YourTable'[Disc]
        )
    RETURN
        ActionTotal
    

    In this expression, 'YourTable' should be replaced with the name of your table. The formula uses the SUMX function to iterate over each row in the filtered table that has the same action as the current row using the EARLIER function. Then, it calculates and sums the (gp - delivery)/Disc value for each row within that action. Finally, the calculated total for each action is returned.

    Make sure to replace 'CalculatedColumn' with the desired name for the calculated column in your table.

  5. User

    The function should sum all values of gp, then sum all values of delivery, then sum all values of Disc, after that find the value of (gp-delivery)/Disc. This value should be same for unique value in column action. Modify the code

  6. Assistant

    Apologies for the confusion. If you want to first sum the values of gp, delivery, and Disc for each unique value in the action column and then calculate the value of (gp - delivery)/Disc, you can use the following modified DAX expression:

    CalculatedColumn = 
    VAR ActionSum =
        SUMX (
            FILTER (
                'YourTable',
                'YourTable'[action] = EARLIER ( 'YourTable'[action] )
            ),
            'YourTable'[gp]
        )
    VAR DeliverySum =
        SUMX (
            FILTER (
                'YourTable',
                'YourTable'[action] = EARLIER ( 'YourTable'[action] )
            ),
            'YourTable'[delivery]
        )
    VAR DiscSum =
        SUMX (
            FILTER (
                'YourTable',
                'YourTable'[action] = EARLIER ( 'YourTable'[action] )
            ),
            'YourTable'[Disc]
        )
    RETURN
        DIVIDE(ActionSum - DeliverySum, DiscSum, 0)
    

    In this expression, 'YourTable' should be replaced with the name of your table. The formula uses the SUMX function with a FILTER to iterate over each row in the filtered table that has the same action as the current row using the EARLIER function. Then, it sums the values of gp, delivery, and Disc separately. Finally, it calculates the value of (gp - delivery)/Disc by subtracting DeliverySum from ActionSum and dividing it by DiscSum.

    Make sure to replace 'CalculatedColumn' with the desired name for the calculated column in your table.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.