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

[Mumiah]

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

[v-tangjie-msft]

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. 

[Mumiah]

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!

[v-tangjie-msft]

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. 

[Mumiah]

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

[v-tangjie-msft · 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))

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. 

[Mumiah]

Thanks this did the trick!!!