summarise and count based on the two tables

[Saxon10]

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

[pranit828 · Accepted 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.

[Saxon10]

Thank you so much for your help and time. Your solution working well. 

[Ashish_Mathur]

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.

[Saxon10]

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?

[Ashish_Mathur]

You are welcome.  I am not aware of any alternative way.