cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper III

## Ignore Top 10 filter in measure

Hi All,

I have expense data for my staffs.

Person Name , Expense

1. A   , 1000
2. B   , 6050
3. C   , 8000

.

.

.

28. GA  , 5900

Totally I have 28 records. I want to see top 10 expense. So I applied top 10 visual filter in chart. I also got top 10 persons.

Now I need % of their expense. If I use calculated measure (or) power bi default measure (% GT from expense), I am getting wrong value in %.

Ex:

I am getting % value in top 10 visual filter chart:

% of A expense = (A expense / sum of top 10 persons expense) * 100

But I need in top 10 visual filter chart like:

% of A expense = (A expense / sum of 28 persons expense) * 100

How to ignore top 10 visual filter in measure?

By

Gnanasekar

1 ACCEPTED SOLUTION
Super User

Hi @Gnanasekar,

Then just add this measure to your table:

```Top 10 percentage weight =
DIVIDE ( [Expense_Total], CALCULATE ( [Expense_Total], ALL ( Staff[Person] ) ) )```

This give the final resulta as expected:

The %GT Expense is the automatic summarization on % of grand total so you can see that applying the measure that I gave you it's the same result.

Regards,

MFelix

Regards

Miguel Félix

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

Proud to be a Super User!

Check out my blog: Power BI em Português

7 REPLIES 7
Frequent Visitor
Helper III

In that post helps to calculate percentage.

But I need to calculate overall percentage in top 10 filter.

By

Gnanasekar.

Super User

Hi @Gnanasekar,

Try to create this measures:

```Expense_Total = SUM(Staff[Expense])

Ranking = RANKX(ALLSELECTED(Staff[Person]),[Expense_Total])

Top 10 percentage =
DIVIDE (
CALCULATE (
[Expense_Total],
FILTER ( ALLSELECTED ( Staff[Person] ), [Ranking] <= 10 )
),
CALCULATE ( [Expense_Total], ALL ( Staff[Person] ) )
)```

In the Top 10 measure I'm calculating the Expense Total for the TOP 10 then filter out that the TOP10 using the ALL formula this consider the full table ignoring the filters/row context you have final result below:

Regards,

MFelix

Regards

Miguel Félix

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

Proud to be a Super User!

Check out my blog: Power BI em Português

Helper III

Hi @MFeli

Please look at below tables

Ex:

Take person AC

Original data:

1. AC's expense 750 &  percentage 6.25%  (Table 1 , %=(750/12000)*100 )

Top 10 Visual level filter applied in chart

1. I am getting:            AC's expense 750 & percentage 8.06%     (Table 2 : %=(750/9300)*100 , here percentage also filter out based on Top 10 filter)
2. I need         :            AC's expense 750 & percentage 6.25%     (Table 3 : %=(750/12000)*100 , here percentage ignoring Top 10 filter)

How to ignore Top 10 visual level filter in measure. ?

By

Gnanasekar

Super User

Hi @Gnanasekar,

Then just add this measure to your table:

```Top 10 percentage weight =
DIVIDE ( [Expense_Total], CALCULATE ( [Expense_Total], ALL ( Staff[Person] ) ) )```

This give the final resulta as expected:

The %GT Expense is the automatic summarization on % of grand total so you can see that applying the measure that I gave you it's the same result.

Regards,

MFelix

Regards

Miguel Félix

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

Proud to be a Super User!

Check out my blog: Power BI em Português

Frequent Visitor

THANK YOU! @MFelix !!!, this formula seems to have done the trick for my issue wich was getting % of column based on total after applying "count of" and "TOP N" filter I can't thank you enough!!! I had tried to make a post about it but hadn't gotten a reply! THANK YOU!

Super User

Hi @Mumiah ,

Glad to know that something with more than 4 years is still helping users.

Thank you for your words.

Regards

Miguel Félix

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

Proud to be a Super User!

Check out my blog: Power BI em Português

Announcements

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

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors