Summarize measure if another measure has value

[LEGIS]

Hi!

I need to summarize a measure, but only if another measure has any value (positive or negative).

I have measures 'Energy' and 'stocked' (both calculated).  I need to summarize 'Energy', but only those rows that has any value on 'stocked'.

For instance, in this case it should summarize 'Energy' only for months May and August (it's in Spanish on the table). The result should be 2659 (1784+875).

How can I do this?

I've tried this calculation that at row level works, but in the total it shows me the sum of everything:

SUMX(Table,

IF(Table[stocked]>0,[Energy],BLANK())

)

(the total should be 2659(1784+875)

Thanks in advance

[v-yangliu-msft]

Hi  @LEGIS ,

Here are the steps you can follow：

1. Create measure.

Measure 2 =
var _new=SUMMARIZE('Table','Table'[year],"Value",[Measure])
return IF(HASONEVALUE('Table'[year]),[Measure],SUMX(_new,[Value]))

2. Result.

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

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

[PaulDBrown]

@LEGIS 

Try:

Measure =

SUMX(FILTER(Table, [Stocked]> 0), [Energy])

[LEGIS]

Hi Paul!
Thanks for your help. 

I've just tried that, but the total keeps summarizing all rows:

[PaulDBrown · Accepted solution]

@LEGIS 

ok, based on the information (fields in the visual) try:

Measure =
SUMX (
    FILTER (
        SUMMARIZE ( //This creates a virtual table with the rows in the visual and the agregated value for [Stocked] by row
            'Table',
            'Table'[Year],
            'Table'[Month],
            "FilteredST", [Stocked]
        ),
        [FilteredST] > 0 //This filters the virtual table to only show the rows whose aggregated [Stocked] value is greater than 0
    ),
    [Energy] //Returns the sum of the value for [energy] for the rows filtered in the virtual table
)

[LEGIS]

Thank you so much Paul!

This worked, I've already marked it as the solution 😁

Sorry for making a nuisance of myself, but my 'Year' and 'Month' fields both are from different tables.

Is there a way to include both tables in the filter?

[PaulDBrown]

@LEGIS 

In theory yes, as long as they are related. Do you have a mockup/depiction of the model view and the relationships?

[OlegV]

If one table is unrelated?

I have three tables. Two of them are related and one table is unrelated.

But the unrelated table can be connected to the main table by creating concatenation columns: one in the main table and one in the unrelated table.

But the obstacle I have is that i am not allowed to use:  calculated tables, calculated columns, power query(m language) and change relationships in the data model. I am.inly allowed to use measures.