cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

## 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

1 ACCEPTED SOLUTION
Super User

You want to do something like this:

``````Summary Table =
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

Ben Dobbs

Did I answer your question? Mark my post as a solution! This will help others on the forum!
6 REPLIES 6
Super User

You want to do something like this:

``````Summary Table =
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

Ben Dobbs

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Anonymous
Not applicable

Perfect , thanks for the support .

Solution Sage

Hi, @Anonymous

Try this:

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

https://allure-analytics.com/

Proud to be a Super User!

Anonymous
Not applicable

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 .

Solution Sage

try using measure like:

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

Check this:

https://allure-analytics.com/

Proud to be a Super User!

Super User

I think my suggestion does what you need.

Ben Dobbs

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors