cancel
Showing results 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

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,

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

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

Proud to be a Super User!

Check out my blog: Power BI em Português

Helper III

Hi @MFeli

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,

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

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.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

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.