Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Gnanasekar
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

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

7 REPLIES 7
shaikhabib
Frequent Visitor

Hi @shaikhabib

 

In that post helps to calculate percentage.

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

 

By

Gnanasekar.

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



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

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



Mumiah
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!

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



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.