Embedding Summary Table within a Measure

[jl20]

Hi all,

I'm trying to figure out how to summarize a table within a measure. An example is the easiest way to show what I'm trying to achieve:

Data table is as follows:

Job #          Revenue         Client Name

1                 $50                 John Doe

25               $150               John Doe

25               $250               John Doe

44               $100               John Doe

100             $99                 John Doe

55              $100                Mr. X

Desired Output Table:

Client          Total Revenue      Max Job     Min Job      Job Count

John Doe     $550                     $400          $50             3

Mr. X            $100                    $100           $100           1

Total revenue is solved using a simple Sum function, but how would I solve for max/min jobs, and also the count? I originally made a separate summary table but then my relationships would not work as expected when I used slicers.

Any help would be greatly appreciated!

[v-yuezhe-msft · Accepted solution]

@jl20,

What is the logic when you calculate Total Revenue, Max Job and Job count for John Doe? Based on your data table,  John has four distinct jobs,  and you can create the following measures in your table.

Total Revenue = SUM(Table1[Revenue])
Max Job = MAX(Table1[Revenue])
Min job = MIN(Table1[Revenue])
Job Count = DISTINCTCOUNT(Table1[Job #])

 

Or if you want to return max job according to Job #, you can create a summary table using the DAX below, then create measures using above similar formulas as in the new table.

Table = SUMMARIZE(Table1,Table1[Client Name],Table1[Job #],"Revenue", SUM(Table1[Revenue]))

Regards,

[jl20]

That worked well, thanks!