Forum Discussion
Embedding Summary Table within a Measure
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!
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,
2 Replies
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,That worked well, thanks!
