Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
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 :

CompanyTypeTotalEmp
ABCA15
XYZA23
ABCA34
XYZA410

 

The result I am looking for :

CompanyDivisionTotalEmpTotal By Company
ABCA159
XYZA2313
ABCA349
XYZA41013

 

Thanks

1 ACCEPTED SOLUTION
bcdobbs
Super User
Super User

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 



Ben Dobbs

LinkedIn | Twitter | Blog

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

View solution in original post

6 REPLIES 6
bcdobbs
Super User
Super User

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 



Ben Dobbs

LinkedIn | Twitter | Blog

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

Perfect , thanks for the support .

ALLUREAN
Solution Sage
Solution Sage

Hi, @Anonymous 

 

Try this:

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

 

 




Did I answer your question? Mark my post as a solution!


https://allure-analytics.com/
https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA
https://www.linkedin.com/company/77757292/

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 . 

try using measure like:

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



Did I answer your question? Mark my post as a solution!


https://allure-analytics.com/
https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA
https://www.linkedin.com/company/77757292/

Proud to be a Super User!




I think my suggestion does what you need. 



Ben Dobbs

LinkedIn | Twitter | Blog

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.