CODEWITHSUNDEEP
powerbipowerbi-desktop
Fragan
Fragan

Reputation: 892

Row calculated from existing rows given some condition

I'm really new to Power BI, I'm a data analyst and usually work with code, so I'm not really familiar with Excel like formulas

I have a table with like 20 columns, and I need only 2 columns for this calculation, so I'm going to zoom in the columns I need:

enter image description here

I want to add rows with Attribute5 for which is  Attribute1 - Attribute2 - Attribute3.

The result should look like this :

enter image description here

I'm really stuck with this since yesterday morning

I achieved it with a Measure by:

  1. Creating a table containing distinct attributes :
TableXX = UNION(DISTINCT('Table'[Attributes]),{{"Attribute5"}})
  1. Doing the calculating with a measure :
    Measure = 
    SUMX (
        DISTINCT ( 'Table 3'[Attributes] ),
        SWITCH (
            'Table 3'[Attributes],
            "Attribute5", CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Attributes] = "Attribute1" )
                - CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Attributes] = "Attribute2" )
                - CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Attributes] = "Attribute3" ),
                var a = 'Table 3'[Attributes] return
            CALCULATE ( SUM ( 'Table'[Value] ),'Table'[Attributes]=a)
        )
    )

But I don't really want a measure, I want to replace Value Column by this measure, how can I achieve that?

Upvotes: 0

Views: 107

Answers (2)

Alexis Olson
Alexis Olson

Reputation: 40204

You can define a new table as the union of the existing table and a calculated table that has the new rows that you want:

NewTable =
VAR Table5 =
    SUMMARIZE (
        FILTER (
            'Table 3',
            'Table 3'[Attributes] IN { "Attribute1", "Attribute2", "Attribute3" }
        ),
        'Table 3'[Date],
        "Attributes", "Attribute5",
        "Value", SUMX (
            'Table 3',
            IF (
                'Table 3'[Attributes] = "Attribute1",
                'Table 3'[Value],
                - 'Table 3'[Value]
            )
        )
    )
RETURN
    UNION ( 'Table 3', Table5 )

The new calculated table is stored in the variable Table5 which takes only the rows containing attributes 1, 2, or 3 and for each date takes the sum of the values where attributes other than 1 get a negative sign.

Upvotes: 2

Andrey Nikolov
Andrey Nikolov

Reputation: 13450

I would say that the easiest way is to pivot Values column, so you will get a table with columns Date, Attribute1, Attribute2, Attribute3 and Attribute4:

enter image description here

Then add a custom column Attribute5:

enter image description here

And then unpivot attributes columns to get the table back:

enter image description here

Upvotes: 1

Related Questions