Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
In data table I have item and branch code which is A01, A02, A03, A04, A05, A06 and A07. The item code contain unique value.
In Report table contain Branch only.
I am trying to count each branch based on the data table into report table.
I am looking for New calculated column and Measure option.
DATA:
ITEM | A01 | A02 | A03 | A04 | A05 | A06 | A07 |
131 | 2 |
|
|
|
|
|
|
166 | 4 | 6 |
|
|
|
|
|
489 | 1 | 1 | 1 |
|
|
| 1 |
540 | 1 |
|
|
| 1 |
|
|
541 | 1 |
|
|
| 1 |
|
|
3815 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
REPORT TABLE:
BRANCH | DESIRED RESULT (COUNT) |
A01 | 10 |
A02 | 8 |
A03 | 2 |
A04 | 1 |
A05 | 3 |
A06 | 1 |
A07 | 2 |
Solved! Go to Solution.
Hi @Saxon10
I would create a new table using below DAX.
Sum_table = UNION(
SUMMARIZE(Tabl,"Branch","A01","Cnt",sum(Tabl[A01])),
SUMMARIZE(Tabl,"Branch","A02","Cnt",sum(Tabl[A02])),
SUMMARIZE(Tabl,"Branch","A03","Cnt",sum(Tabl[A03])),
SUMMARIZE(Tabl,"Branch","A04","Cnt",sum(Tabl[A04])),
SUMMARIZE(Tabl,"Branch","A05","Cnt",sum(Tabl[A05])),
SUMMARIZE(Tabl,"Branch","A06","Cnt",sum(Tabl[A06])),
SUMMARIZE(Tabl,"Branch","A07","Cnt",sum(Tabl[A07]))
)
Use this table in a matrix as below screenshot.
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
Hi @Saxon10
I would create a new table using below DAX.
Sum_table = UNION(
SUMMARIZE(Tabl,"Branch","A01","Cnt",sum(Tabl[A01])),
SUMMARIZE(Tabl,"Branch","A02","Cnt",sum(Tabl[A02])),
SUMMARIZE(Tabl,"Branch","A03","Cnt",sum(Tabl[A03])),
SUMMARIZE(Tabl,"Branch","A04","Cnt",sum(Tabl[A04])),
SUMMARIZE(Tabl,"Branch","A05","Cnt",sum(Tabl[A05])),
SUMMARIZE(Tabl,"Branch","A06","Cnt",sum(Tabl[A06])),
SUMMARIZE(Tabl,"Branch","A07","Cnt",sum(Tabl[A07]))
)
Use this table in a matrix as below screenshot.
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
Thank you so much for your help and time. Your solution working well.
Hi,
In the Query Editor, right click on the first heading and select "Unpivot other columns". To your table visual, drag the Attribute column and write this measure
=sum(Data[Value])
Hope this helps.
Thank you so much for your quick help. The item and branch columns as a calculated columns so unable to bring it into query editor.
Can please advise is there any alternative way?
You are welcome. I am not aware of any alternative way.