Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I need help as I am trying to build a table with a case I never face untill now.
I currently have a table with different line that I want to summarize.
For example:
date | task | center | qty |
01/01/2023 | 1 | a | 2 |
01/01/2023 | 2 | a | 3 |
02/01/2023 | 2 | a | 4 |
02/01/2023 | 2 | b | 5 |
02/01/2023 | 1 | a | 1 |
03/03/2023 | 1 | b | 2 |
03/03/2023 | 2 | a | 3 |
03/03/2023 | 1 | b | 4 |
03/03/2023 | 2 | a | 5 |
I want to SUMMARIZE by date, task, SUM(qty) BUT in the new table , I want to add a variable to "center", let's say C, which would be center A and B together.
So on the result would looks like this:
date | center | task | qty |
01/01/2023 | a | 1 | 2 |
01/01/2023 | a | 2 | 3 |
01/01/2023 | c | 1 | 2 |
01/01/2023 | c | 2 | 3 |
02/01/2023 | a | 2 | 4 |
02/01/2023 | b | 2 | 5 |
02/01/2023 | a | 1 | 1 |
02/01/2023 | c | 2 | 9 |
02/01/2023 | c | 1 | 1 |
03/03/2023 | b | 1 | 6 |
03/03/2023 | a | 2 | 8 |
03/03/2023 | c | 1 | 6 |
03/03/2023 | c | 2 | 8 |
Any help regarding a function or anything I could use ?
Kind regards
Solved! Go to Solution.
hi @Foguete
try to create a calculated table like:
Table =
VAR _table=
ADDCOLUMNS(
SUMMARIZE(
TableName,
TableName[Date],
TableName[task]
),
"center", "c",
"Qty", CALCULATE(SUM(TableName[Qty]))
)
RETURN
UNION(TableName, _table)
verified and worked like this:
hi @Foguete
try to create a calculated table like:
Table =
VAR _table=
ADDCOLUMNS(
SUMMARIZE(
TableName,
TableName[Date],
TableName[task]
),
"center", "c",
"Qty", CALCULATE(SUM(TableName[Qty]))
)
RETURN
UNION(TableName, _table)
verified and worked like this:
Thank you @FreemanZ , however I have been simplifying "TableName" and in reality it has more column. So on, UNION is throwing the error "it must have the same number of columns".
How can I get through that ?
hi @Foguete
i am afraid there might be no easy workaround. How many column do you have?
Hi @FreemanZ,
Thanks for the DAX, a slight modification and I have found the solution.
Here in red, it is in case someone would get there:
Brilliant! Great idea!
hi @Foguete
if you mind the column ordering, try this:
Table =
VAR _table=
ADDCOLUMNS(
SUMMARIZE(
TableName,
TableName[Date],
TableName[task]
),
"center", "c",
"Qty", CALCULATE(SUM(TableName[Qty]))
)
VAR _table2= UNION(TableName, _table)
RETURN
SELECTCOLUMNS(
_table2,
"date", TableName[date],
"center", TableName[center],
"task", TableName[task],
"qty", TableName[qty]
)
User | Count |
---|---|
53 | |
35 | |
20 | |
15 | |
14 |
User | Count |
---|---|
94 | |
76 | |
36 | |
22 | |
18 |