Forum Discussion

WCrayger's avatar
WCrayger
Frequent Visitor
7 years ago
Solved

Aggregate Distinct Values

Hello,

 

I would like to aggregate distinct values for Extended Invoice Total Amount and Client Subtotal. I'm aware of SUMX(DISTINCT([ColumnName]), MAX(Value)) but that does not work in this scenario. The desired output is 19570 for Extended Invoice Amount and 21527 for Client Subtotal.

 

Thanks in advance.

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    try these two measures:

    Total Client SubTotal = 
SUMX( 
    SUMMARIZE(
        Table1,Table1[client total])
    ,
    [client total]
)

Total Extended Invoice Amount = 
SUMX( 
    ADDCOLUMNS(
        SUMMARIZE( Table1, Table1[Extended Inv Total]),
        "TEST2",
            CALCULATE(
                MAX(Table1[Extended Inv Total])
            )
    ),
    [TEST2]
)

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    try these two measures:

    Total Client SubTotal = 
SUMX( 
    SUMMARIZE(
        Table1,Table1[client total])
    ,
    [client total]
)

Total Extended Invoice Amount = 
SUMX( 
    ADDCOLUMNS(
        SUMMARIZE( Table1, Table1[Extended Inv Total]),
        "TEST2",
            CALCULATE(
                MAX(Table1[Extended Inv Total])
            )
    ),
    [TEST2]
)

    • WCrayger's avatar
      WCrayger
      Frequent Visitor
      7 years ago

      Thanks for your reply, Nick. Your solution works for Client Subtotal. For Extended Invoice Amount, there is one additional requirement. There can be multiple From Company Key's associated to a Work Order Number. I need to report at the From Company level, and this field will be coming from a seperate dimension table. 

      • WCrayger's avatar
        WCrayger
        Frequent Visitor
        7 years ago

        Following up. I modified your solution for Extended Invoice Amount by wrapping it in a calculate function with a userelationship arguement. This has solved my issue. 

         

        Thanks again for your help, Nick.