cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Surafel
Helper I
Helper I

Count The Number of subcategories in matrix

I want to count the number of subcategories. Data looks like below:

 

 

Rank     			Vendor Group
Elite	       		Avot
Elite	           	Ivak
Essential			Boch LTD
Essential			Futh Elec
Provisional			Active Dach
Provisional			Alloc Mac
Provisional			Alldoc
Provisional			Anpro
Provisional			Falow
Provisional			Match
Provisional			Beast Elec

 

 

The data above is a matrix. Vendor group is a column that comes from Vendor Info Table and Rank is a measure calculated based on their score calculated using another measure. I want to count how many Elites and how many Provissonals we have in different card or table. How can I do this please?

 

1 ACCEPTED SOLUTION

Hi @Surafel ,

 

First create a dim table as below:

v-kelly-msft_0-1621411919932.png

Then create a measure as below:

Measure 2 = 
SWITCH(SELECTEDVALUE('Dim table'[Column1]),"Elite",CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[Measure]="Elite")),
"Essential",CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[Measure]="Essential")),
"Provisional",CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[Measure]="Provisional")))

Finally you will see:

v-kelly-msft_1-1621411964645.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

View solution in original post

3 REPLIES 3
Surafel
Helper I
Helper I

Surafel_0-1620258346644.png

I am only getting the total number of vendor groups as shown in snippet above. 

Hi @Surafel ,

 

First create a dim table as below:

v-kelly-msft_0-1621411919932.png

Then create a measure as below:

Measure 2 = 
SWITCH(SELECTEDVALUE('Dim table'[Column1]),"Elite",CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[Measure]="Elite")),
"Essential",CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[Measure]="Essential")),
"Provisional",CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[Measure]="Provisional")))

Finally you will see:

v-kelly-msft_1-1621411964645.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

View solution in original post

samdthompson
Solution Sage
Solution Sage

Hello, try making this measure:

 

=COUNTROWS(Table)

 

here it is at work:

2021-05-06_11-34-15.jpg

// if this is a solution please mark as such. Kudos always appreciated.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors