Ignore Top 10 filter in measure

[Gnanasekar]

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

[shaikhabib]

Hi, 

Might be this will helpful to you 

https://community.powerbi.com/t5/Desktop/Create-measure-of-total/m-p/8027#M1227

Thanks!!

[Gnanasekar]

Hi @shaikhabib

In that post helps to calculate percentage.

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

By

Gnanasekar.

[MFelix]

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

[Gnanasekar]

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

[MFelix · 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:

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

[Mumiah]

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!

[MFelix]

Hi @Mumiah ,

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

Thank you for your words.