cancel
Showing results for 
Search instead for 
Did you mean: 
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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.