Need Help with SUMX based on a different column

[vacacelaivan]

Hi all,

Been stuck with this measure for a while.

I'm looking to create a measure that provides the SUM of a column based on the SUM of another column.

Measure1 = CALCULATE(
    SUMX('Table1','Table1'[Alerts Worked]),
    FILTER('Table1', SUM('Table1'[Time in Queue]) > 0 ))

This provides the following result:

	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18
Product1			46	566		5461	6546
Product2		656		5472	478
Product3	3698	165		864		23	564
Product4	4656					54	44
Total

The total row is blank and needs to be populated for the desired result. 

Any help is much appreciated!

[v-shex-msft · Accepted solution]

Hi @vacacelaivan,

Unfortunately, dax formula not support summary across multiple columns, you need to manually summary each columns.

I'd like to suggest you do 'unpivot columns' in query edit then you can simply summary transformed values by dax formula.

Unpivot Data Using Excel Power Query

Measure =
CALCULATE (
    SUM ( Table[Alerts Worked] ),
    FILTER ( ALLSELECTED ( Table ), [Date] <= MAX ( Table[Date] ) ),
    VALUES ( Table[Product] )
)

Regards,

Xiaoxin Sheng

[AlB]

Hi @vacacelaivan

Can you share the pbix?

It's a bit difficult to see what might be going on without it