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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

Top x % of total

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.

1 ACCEPTED SOLUTION

Hi  @Anonymous  ,

According to your description, I added the date:

v-yangliu-msft_0-1611798182217.png

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:

v-yangliu-msft_1-1611798182219.png

 

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.

View solution in original post

5 REPLIES 5
v-yangliu-msft
Community Support
Community Support

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:

v-yangliu-msft_0-1611735611388.png

 

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
Not applicable

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)

Hi  @Anonymous  ,

According to your description, I added the date:

v-yangliu-msft_0-1611798182217.png

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:

v-yangliu-msft_1-1611798182219.png

 

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
Not applicable

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
Super User
Super User

@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.

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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