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 scenar...
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]
)

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.