Summarized Table - Total By Column

[Anonymous]

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

[bcdobbs · Accepted solution]

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 

[Anonymous]

Perfect , thanks for the support .

[ALLUREAN]

Hi, @Anonymous 

Try this:

Summary Table =
SUMMARIZE('Table1',
'Table1'[Company],
"TotalEmp",
DISTINCTCOUNT('Table1'[UserID])
)

[Anonymous]

This not what i am looking for , the column (Type) is missing .. I want the (Total By Company) grouped based on the company with (type) coulmn present in the table . 

[ALLUREAN]

try using measure like:

Total By Company = CALCULATE(COUNT('Table1'[UserID]), ALLEXCEPT('Table1', 'Table1'[Company]))

Check this:

https://www.dropbox.com/s/c04go96otd7pco2/Summarized%20Table%20-%20Total%20By%20Column%28alllure-ana...

[bcdobbs]

I think my suggestion does what you need.