Aggregate Distinct Values

[WCrayger]

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.

[Anonymous · Accepted solution]

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]

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]

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.