Forum Discussion

vacacelaivan's avatar
vacacelaivan
Frequent Visitor
7 years ago
Solved

Need Help with SUMX based on a different column

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-18May-18Jun-18Jul-18Aug-18Sep-18Oct-18
Product1  46566 54616546
Product2 656 5472478  
Product33698165 864 23564
Product44656    5444
Total       

 

 

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

Any help is much appreciated!

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

2 Replies

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    Hi vacacelaivan

     

    Can you share the pbix?

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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