Forum Discussion
Summarized Table - Total By Column
Hi
I need help to calculate (Total By Company) in my summary table , below is the code for the summarized table :
Summary Table =
SUMMARIZE('Table1',
'Table1'[Company],
'Table1'[Type],
"TotalEmp",
DISTINCTCOUNT('Table1'[UserID])
)
below is the result of the above code :
| Company | Type | TotalEmp |
| ABC | A1 | 5 |
| XYZ | A2 | 3 |
| ABC | A3 | 4 |
| XYZ | A4 | 10 |
The result I am looking for :
| Company | Division | TotalEmp | Total By Company |
| ABC | A1 | 5 | 9 |
| XYZ | A2 | 3 | 13 |
| ABC | A3 | 4 | 9 |
| XYZ | A4 | 10 | 13 |
Thanks
You want to do something like this:
Summary Table = ADDCOLUMNS ( SUMMARIZE( 'Table1', 'Table1'[Company], 'Table1'[Type] ), "Emp", CALCULATE ( DISTINCTCOUNT('Table1'[UserID]) ), "Total Company Emp ", CALCULATE ( DISTINCTCOUNT('Table1'[UserID]), ALLEXCEPT ( Table1, Table1[Company] ) ) )You'll notice I've used SUMMARIZE in combination with ADDCOLUMNS rather than letting SUMMARIZE do the aggregation. Have a read of All The Secrets of Summarize
6 Replies
You want to do something like this:
Summary Table = ADDCOLUMNS ( SUMMARIZE( 'Table1', 'Table1'[Company], 'Table1'[Type] ), "Emp", CALCULATE ( DISTINCTCOUNT('Table1'[UserID]) ), "Total Company Emp ", CALCULATE ( DISTINCTCOUNT('Table1'[UserID]), ALLEXCEPT ( Table1, Table1[Company] ) ) )You'll notice I've used SUMMARIZE in combination with ADDCOLUMNS rather than letting SUMMARIZE do the aggregation. Have a read of All The Secrets of Summarize
Hi, Anonymous
Try this:
Summary Table =
SUMMARIZE('Table1',
'Table1'[Company],
"TotalEmp",
DISTINCTCOUNT('Table1'[UserID])
)I think my suggestion does what you need.
