Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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
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
Perfect , thanks for the support .
Hi, @Anonymous
Try this:
Summary Table =
SUMMARIZE('Table1',
'Table1'[Company],
"TotalEmp",
DISTINCTCOUNT('Table1'[UserID])
)
Proud to be a Super User!
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:
Proud to be a Super User!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
84 | |
84 | |
66 | |
62 | |
62 |
User | Count |
---|---|
199 | |
120 | |
110 | |
79 | |
69 |