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

Reply
Mumiah
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.

Mumiah_0-1676041411435.png

 

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

Mumiah_1-1676041489078.png

 

 

1 ACCEPTED SOLUTION

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))

vtangjiemsft_0-1676444079486.png

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. 

View solution in original post

6 REPLIES 6
v-tangjie-msft
Community Support
Community Support

Hi @Mumiah ,

 

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

(1) This is my test data. 

vtangjiemsft_0-1676257335392.png

(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.

vtangjiemsft_1-1676257540557.png

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. 

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!

 

Mumiah_0-1676305123437.png

 

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.

vtangjiemsft_0-1676359711052.png

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. 

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****

Mumiah_1-1676387432183.png

 

Mumiah_0-1676387239688.png

 

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))

vtangjiemsft_0-1676444079486.png

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. 

Thanks this did the trick!!!

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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