Mesure Addition of Columns if have same value on other column

[Fragan]

Hey,

Im a beginner with PowerBI.

I have a table with 3 columns:

Date	Attributes	Value
04/2020	Attribute1	2
04/2020	Attribute2	3
04/2020	Attribute3	4
04/2020	Attribute4	88
03/2020	Attribute1	3
03/2020	Attribute2	9
03/2020	Attribute3	23
03/2020	Attribute4	929

I want to add Attribute5 for each date wich is  Attribute1 - Attribute2 - Attribute3.

So the result may look like this:

Date	Attributes	Value
04/2020	Attribute1	2
04/2020	Attribute2	3
04/2020	Attribute3	4
04/2020	Attribute4	88
05/2020	Attribute5	-5
03/2020	Attribute1	3
03/2020	Attribute2	9
03/2020	Attribute3	23
03/2020	Attribute4	929
03/2020	Attribute5	-29

Im really stuck with this, im a Data Analyst and i usually work with code x) Im not really familiar with Excel like formulas.

Can anyone help me with at ? or atleast guide me to some source where i can find  a hint to do what im looking forward.

Regards

[v-easonf-msft]

Hi , @Fragan 

Here is  a demo.

Pbix attached

If help ,try steps as below.

1. Create a seperated table

Table 3 = UNION(DISTINCT('Table'[Attributes]),{{"Attribute5"}})

2.Create measure as below

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

3. add the field to the value of table visual( sort by Date)

the result will show as below

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Fragan]

Hi @v-easonf-msft ,

First of all, thank you for your answer, i have one more question ? what if i what to add another attribute based on that Attribute 5 ? Should i make a new mesure using this mesure ou can i just add Attribute5 and Attribute6(given that Attributes6 depends on Attribute5) in the same mesure ?

[v-easonf-msft · Accepted solution]

Hi ， @Fragan 

You only need to do some changes in the same measure.

Table 3 = UNION(DISTINCT('Table'[Attributes]),{{"Attribute5"}},{{"Attribute6"}})

Measure = 
var A5=CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Attributes] = "Attribute1" )
            - CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Attributes] = "Attribute2" )
            - CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Attributes] = "Attribute3" )

return SUMX (
    DISTINCT ( 'Table 3'[Attributes] ),
    SWITCH (
        'Table 3'[Attributes],
        "Attribute5", A5 ,
        "Attribute6",IF(A5<=-10,-1,1),
            var a = 'Table 3'[Attributes] return
        CALCULATE ( SUM ( 'Table'[Value] ),'Table'[Attributes]=a)
    )
)

pbix attached

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Fragan]

Anyone got an idea ? I tried duplicating my table and then doing a fusion but its taking ages..

[amitchandak]

Create a new table like

New table =union
(
selectcolumns(table,"Date",table[Date],"Attributes",table[Attributes],"Value",table[Value]),
summarize(filter(Table,Table[Atribute] in {"Attribute1","Attribute2","Attribute3"}),table[Date],"Attribute","Attribute5","Value",sum(Table[Value]))
)