Percent of filtered Total

[MrNoob]

This may be hard to explain but I will give it a go.  I need percent of a column, that is filter down to a group within that column.  IE table[Product], but filtered down to APPLES.  When the product column contains APPLES, ORANGES, and PEARS.  

Then, of course, divide Total Apples /  all(table[Prodcut] = "APPLES"  (this does not work but I hope it illustrates what I am trying to accomplish)

The all statement is meant to be used with the various slicers.

[MrNoob]

Sanitized data

This is the top 10 of the data set, I want to divide the numbers by the bottom grand total.  

[Ashish_Mathur]

Hi,

That is Top 11 not top 10.  Try this

=[Revenue]/SUMX(TOPN(10,ALL(Channel),[revenue]),[revenue])

If this does not work, then share a sample workbook.

Hope this helps.

[MrNoob]

No:( ,  The easiest work around, because I had built this into Powerpivot, is to just use excel on the side.  

[Ashish_Mathur]

Hi,

I do not understand your response.  Please clarify.  Did my suggested formnula solve the problem?  If not, please be specific.

[MrNoob]

No, the formula did not work.  The first category became 100% and everything else was weighted off of 1002.   

[Thyago_Rezende]

Hi @MrNoob,

You need to clarify all filter context and display data model for we´re going to help you. Can you do it?

Regards,

[MrNoob]

My apologies,

The filter context is as follows

hol_agency_name

hol_clientele

MTD UA REV

Company X	Business	1002
Company Y	Business	537

The MTD UA REV is already filtered down by month and department (UA being the department).  I am trying to find what percentage of MTD UA REV came from company X,  So in this case  1002 (company X) / (2879) Grand total of MTD UA REV= 34%

I also want to thank you guys for your help.  This site provides me with lots of insight, and I am grateful for your assistance.  This is a great learning experience for sure. 🙂  

[Ashish_Mathur]

Hi,

It will be nice if you can share the link from where your file can be downloaded.

[Thyago_Rezende]

Try ALLSELECTED function. I believe that it works fine for your purpose.

[GabrielSantos]

To clarify, are you looking for the % against the grand total of all categories, such as

Product | Quantity

Apples      20

Oranges   30

Pears        50

Apples are 20% of total, oranges 30% of total, pears 50% of total - and the measure returns the count against the count of ALL products regardless of what is on the slicer?

[MrNoob]

I wish that was the case.  

I want to take the total of apples sold, and then find the percent each store had sold.  

IE

Product | Quantity                                

Apples      20

Oranges   30

Pears        50

   Store | Name

North 

South

North sold: 15/20 = 75%

South sold: 5/20 = 25%

I think that may be a better explanation