Table with percentage as total (of the top 5 I selected)

[Anonymous]

Hi, I have a set of data and I would like the view to as follows:-

I already managed to get Top 5 from my dataset using TopN. But now I would like to know the percentage of subtotal in the total row.

May I know how can I do that?

Thanks in advance!

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

I tried to create a simple sample like below.

Top five Revenue: =
VAR topfiverev =
CALCULATE (
SUM ( Data[Revenue] ),
KEEPFILTERS (
TOPN ( 5, ALL ( Data[Company] ), CALCULATE ( SUM ( Data[Revenue] ) ), DESC )
)
)
VAR allrev =
CALCULATE ( SUM ( Data[Revenue] ), REMOVEFILTERS () )
RETURN
IF (
HASONEVALUE ( Data[Company] ),
topfiverev,
FORMAT ( DIVIDE ( topfiverev, allrev ), "#.00%" )
)

[Anonymous]

Thanks for this! This is exactly what I am looking for!

If let say I have a column which is industry type of the company, in which part should I edit the formula in order to be able to filter it?

https://drive.google.com/drive/folders/1k8gKWmgrhHuP_6KU4HyVUKWEbsLfUXcF?usp=sharing

I have uploaded it here, please have a look and let me know if you cannot open it

[Jihwan_Kim]

Hi,

Thank you for your feedback.

Please check the link, because I cannot open the link.

Thank you.

[Anonymous]

https://www.dropbox.com/s/fkedtodbqlo9i4a/follow%20up%20q.pbix?dl=0

What about this one?

[Jihwan_Kim]

Hi,

Please try the below measure.

Top five Revenue for Bank only: =
VAR topfivebank =
CALCULATE (
SUM ( Data[Revenue] ),
KEEPFILTERS (
TOPN (
5,
CALCULATETABLE ( Data, FILTER ( ALL ( Data ), Data[Column1] = "Bank" ) ),
CALCULATE ( SUM ( Data[Revenue] ) ), DESC
)
)
)
VAR bankall =
CALCULATE (
SUM ( Data[Revenue] ),
FILTER ( ALL ( Data ), Data[Column1] = "Bank" )
)
RETURN
IF (
HASONEVALUE ( Data[Company] ),
topfivebank,
FORMAT ( DIVIDE ( topfivebank, bankall ), "#.00%" )
)

[Anonymous]

Hi Jihwan!

Thanks for the prompt reply!

I teste the code, it works! But heres a follow up question

Top five Revenue for Bank only: =
VAR topfivebank =
CALCULATE (
SUM ( Data[Revenue] ),
KEEPFILTERS (
TOPN (
5,
CALCULATETABLE ( Data, FILTER ( ALL ( Data ), Data[Column1] = "Bank" ) ),
CALCULATE ( SUM ( Data[Revenue] ) ), DESC
)
)
)
VAR bankall =
CALCULATE (
SUM ( Data[Revenue] ),
FILTER ( ALL ( Data ), Data[Column1] = "Bank" )
)
RETURN
IF (
HASONEVALUE ( Data[Company] ),
topfivebank,
FORMAT ( DIVIDE ( topfivebank, bankall ), "#.00%" )
)

Above are your code and 

CALCULATETABLE ( Data, FILTER ( ALL ( Data ), Data[Column1] = "Bank" ) ),
CALCULATE ( SUM ( Data[Revenue] ) )

Why this line of code only take my 1st line of item instead of adding it all together?

My dataset changes again, let me attach the table here 

Company Revenue Column1

a	1000	Bank
b	800	Bank
c	750	Bank
d	700	Bank
e	500	Bank
f	450	Bank
g	350	Bank
h	185	Bank
i	15	Bank
j	155	Bank
k	32	Bank
l	447	Bank
m	1000	Insurance
n	800	Insurance
o	750	Insurance
p	700	Insurance
q	500	Insurance
r	450	Insurance
s	350	Insurance
t	185	Insurance
u	15	Insurance
v	155	Insurance
w	32	Insurance
x	447	Insurance
a	500	Bank

New line of item in red.

In your code, it will only take Company A, which is 1000, but we know that the total of company A is 1500.

[Jihwan_Kim]

Hi,

Sorry I quite do not understand.

Could you please show me your sample pbix file with the additional line of a = 500 = bank? And the visualization that shows only a=1000, instead of a=1500?

[amitchandak]

@Anonymous , assume you have measure TOP 5

Then create a measure like

TOP 5 new = if(isinscope(Table[Company]), [TOP 5], divide([TOP 5], calculate([TOP 5], allselected())))