Forum Discussion

jl20's avatar
jl20
Helper IV
9 years ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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,

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
jl20
Helper IV
9 years ago

That worked well, thanks!