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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Show percentage of total of a count without modifying percentage when filtering by Top N

Hi, I was hoping somebody could help me with this as I'm fairly new to PowerBi and can't seem to figure this one out, I have a set of data of 2 columns, with 475 entries, what I'm trying to do is have a count of how many times a value from column [Names"] shows for values of column [Departments], so I do [count of Names], and that gives me the right numbers, I add the column again and pick to display as percentage and I get almost what I need as the percentage is correct; however,  once I filter to "top N 3" it modifies the percentage to only account the filtered values. I see there are a few similar posts on this but couldn't find one that would try to get the percentage of a "count of" column. Please any help or Ideas on how to go about this would mean the world as I've been 2 days trying to figure this out. Thank you

Sample of dat source.

Screenshot of Bi. *goal is to have the lower table show the percent from the upper table, the Total row is not meant to be in screenshot as I've removed it, but leaving it in case it helps understand better

1 ACCEPTED SOLUTION
Community Support

Hi @Mumiah ,

I've modified the measure based on your needs.

``````Top three count percent = var a=COUNTROWS(ALL('Table'))
var c=TOPN(3,ALLSELECTED('Table'[Department]),CALCULATE(COUNTROWS('Table')),DESC,[Department],DESC)
return
CALCULATE(DIVIDE(COUNTROWS('Table'),a),FILTER('Table',[Department] in c))``````

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.

6 REPLIES 6
Community Support

Hi @Mumiah ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data.

(2) We can create two measures.

``````Top three count percent: =
VAR _totalcount =
CALCULATE ( COUNTROWS ('Table'), REMOVEFILTERS () )
VAR _topthreecount =
CALCULATE (
COUNTROWS ( 'Table' ),
KEEPFILTERS (
TOPN ( 3, ALL ( 'Table'[Department] ), CALCULATE ( COUNTROWS ( 'Table' ) ), DESC )
)
)
RETURN
DIVIDE ( _topthreecount, _totalcount )``````
``Flag = IF('Table'[Top three count percent:]=BLANK(),0,1)``

(3) Place [flag] on the visual filter and then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider  Accept it as the solution  to help the other members find it more quickly.

Frequent Visitor

THANK YOU!!!, this actually has been driving me crazy, I did encounter a new problem though.

Using your sample data. If I were to want to filter out the color blue in the visual as I don't report on that one but want to maintain it in the table to not modify percentages, it will only display the top 2 instead of the top 3. Any tips on this one? again thank you for your assistance!

Community Support

Hi @Mumiah ,

I've modified the measure based on your needs.

``````Top three count percent =
var a=COUNTROWS(ALL('Table'))
var b=TOPN(3,ALLSELECTED('Table'[Department]),CALCULATE(COUNTROWS('Table')),DESC)
return CALCULATE(DIVIDE(COUNTROWS('Table'),a),FILTER('Table',[Department] in b))``````
``Flag = IF('Table'[Top three count percent]=BLANK(),0,1)``

Place [flag] on the visual filter and then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.

Frequent Visitor

Hey @v-tangjie-msft ,

Thanks again for taking the time, it really means a lot, I've implimented your updated solution and seems to work but I encountered another problem and again this is as you mentioned due to the requirements of my task, I have a problem where after filterd or even where not filtered, it seems if the last value of the "TOPN" is a tie, it will include it instead of limiting it to 3, I was trying to see if I can add another filter to where if a tie at the last value then sort alphabatically and pick the one on top but was unable to do so, could you help me with that.

****I ADDED 2 WHITE VALUES IN ORDER TO GET THE SCENARIO SHOWN BELOW****

Community Support

Hi @Mumiah ,

I've modified the measure based on your needs.

``````Top three count percent = var a=COUNTROWS(ALL('Table'))
var c=TOPN(3,ALLSELECTED('Table'[Department]),CALCULATE(COUNTROWS('Table')),DESC,[Department],DESC)
return
CALCULATE(DIVIDE(COUNTROWS('Table'),a),FILTER('Table',[Department] in c))``````

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.

Frequent Visitor

Thanks this did the trick!!!

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors