Top x % of total

[Anonymous]

Hello everyone,

I'm trying to show what % of the total within the last 30 days is made up by the top X in a simple output like "37%".

I define something as being in the top by the number of rows in a column with the same text value, rather than a sum.

EG

Red
Red
Red
Red
Red
Yellow
Yellow
Yellow
Green
Green

Red is top as it features 5, then Yellow with 3 and Green with 2.  If I was looking at top 1 I would want a measure that gives the value "50%" as 5/10 are made up by the top value.

This becomes more complicated as I want to show this only for the last 30 days and I do want it to take other filters into account.  EG if I filtered Green out I would want it to take that into account.

I have a calculated field in the table already which gives a simple "Yes" or "No" if the value was submitted in the last 30 days.  I use that to give me the number to divide by. 

I just can't work out the DAX to give me the count total of the top 20 featured values in my field.  If someone could figure out this missing part in bold below that would be great

Theme_%_total = divide(

<need top 20 count in last 30 days here>

,

COUNTX(filter(TABLE,TABLE[Last_30_Days]="Yes"),TABLE[Last_30_Days]))

Note - I have done this by using an ALL() in the countx and filtering the visual by top 20, but that gives some wacky results when I apply other filters, so I really need to do this without visual filters.

[Anonymous]

Hi  @Anonymous  ,

Here are the steps you can follow：

1. Create measure

Calculate the number of times of each Sub_Theme whose Last_30_Days is Yes

count_time =
CALCULATE(COUNT('Table'[Sub_Theme]),FILTER('Table','Table'[Sub_Theme]=MAX('Table'[Sub_Theme])&&'Table'[Last_30_Days]="Yes"))

Calculate the top 3:

rank_3 =
var _1=IF(MAX('Table'[Last_30_Days])="Yes",
RANKX(FILTER(ALL('Table'),'Table'[Last_30_Days]="Yes"),'Table'[count_time],,DESC,Dense),BLANK())
return IF(_1<=3,_1,BLANK())

Calculate the proportion:

% of the top 3 =
 var _countall=COUNT('Table'[Sub_Theme])
 var _count_top3=COUNTAX('Table','Table'[rank_3])
 return DIVIDE(_count_top3,_countall)

2. Result：

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks for this, afraid it doesn't give the right result.  The last step doesn't seem to factor in last 30 days. 

Here's the data that I would like it to be working on, so the DAX should boil down to  11/18 (61%)

instead of 18 it is picking up 42 (all the records not just those in the last 30 days)

[Anonymous · Accepted solution]

Hi  @Anonymous  ,

According to your description, I added the date:

Here are the steps you can follow：

1. Create calculated column.

date_inter = DATEDIFF('Table'[date],TODAY(),DAY)

2. Create measure

Calculate the number of times of each Sub_Theme whose Last_30_Days is Yes

count_time =
CALCULATE(COUNT('Table'[Sub_Theme]),FILTER(ALL('Table'),'Table'[Sub_Theme]=MAX('Table'[Sub_Theme])&&'Table'[Last_30_Days]="Yes"))

Calculate the top 3:

rank_3 =
var _1=IF(MAX('Table'[Last_30_Days])="Yes",
RANKX(FILTER(ALL('Table'),'Table'[Last_30_Days]="Yes"),'Table'[count_time],,DESC,Dense),BLANK())
return IF(_1<=3,_1,BLANK())

The proportion of the first 30 days of calculation:

% of the top 3 =
 var _countall=COUNT('Table'[Sub_Theme])
 var _count_top3=COUNTAX('Table','Table'[rank_3])
 return DIVIDE(_count_top3,_countall)

3. Result：

You can downloaded PBIX file from here

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Sure here's a short sample.  So I could be trying to get a % of the top 3 where last 30 days is Yes. 

Sub_Theme Last_30_Days
Ethernet - Alert No
service - Running No
Ethernet - Alert No
Ethernet - Alert No
Port - Alert Yes
Ethernet - Alert No
remote - Collect Yes
Job - Fault No
Ethernet - Alert Yes
Ethernet - Alert No
Code Yes
Code No
Code - Caution No
Code No
Ethernet - Alert Yes
Code No
Processor - 95% Yes
Server - Health Yes
Ethernet - Alert No
Processor - 95% No
Terminal - New / Install No
Increase / add No
Ethernet - Alert Yes
CPU Yes
Data - Enter No
Contact - Transport Yes
Account - Copy / repeat No
Ping - Down No
system - Reboot Yes
system - Reboot Yes
system - Reboot Yes
system No
system - Reboot Yes
service - Running No
system - Reboot Yes
Logical - Increase / add No
Memory - Usage Yes
system - Reboot Yes
system - Reboot No
Logical - Space No
system - Reboot No

[amitchandak]

@Anonymous , Can you share better sample data and sample output in table format( I am not able to relate your data with description) ? Or a sample pbix after removing sensitive data.