cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
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

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Ignore Top 10 filter in measure

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:

 

top10revise.png

 

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





View solution in original post

5 REPLIES 5
Highlighted
Frequent Visitor

Re: Ignore Top 10 filter in measure

Highlighted
Helper III
Helper III

Re: Ignore Top 10 filter in measure

Hi @shaikhabib

 

In that post helps to calculate percentage.

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

 

By

Gnanasekar.

Highlighted
Super User III
Super User III

Re: Ignore Top 10 filter in measure

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:

 

top10.png

 

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





Highlighted
Helper III
Helper III

Re: Ignore Top 10 filter in measure

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. ?Top 10.JPG

 

 

By

Gnanasekar

Highlighted
Super User III
Super User III

Re: Ignore Top 10 filter in measure

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:

 

top10revise.png

 

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





View solution in original post

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors