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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Saxon10
Post Prodigy
Post Prodigy

summarise and count based on the two tables

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

1 ACCEPTED SOLUTION
pranit828
Community Champion
Community Champion

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.

pranit828_0-1615747797586.png

 





PBI_SuperUser_Rank@1x.png


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

View solution in original post

5 REPLIES 5
pranit828
Community Champion
Community Champion

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.

pranit828_0-1615747797586.png

 





PBI_SuperUser_Rank@1x.png


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. 

Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

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