Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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] )
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] )
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.
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.