Percent of total volume in a top x table

[Anonymous]

Hello,

A quick one I hope.

I have a table that counts the number of rows in the data set which looks like this, there's other columns but not important to the question.

                     Created     

Product 1      1000

Product 2      500

Product 3      200

I have a measure called 'created' which simply counts the lines of data (no sums of numerical fields etc)

I've filtered the table to show only created in the last 30 days and only the top x in that time.

I would like to add a column the shows the % the Product represents out of the total volume in the 30 days rather than a % of the top x.  EG if there were 10,000 lines of data in the 30 days, the table for top x products in 30 days would look like this.

                     Created     % of total in last 30 days

Product 1      1000         10%

Product 2      500           5%

Product 3      200           2%

[amitchandak]

@Anonymous , input vs output is not clear. . Can you please explain with an example.

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Appreciate your Kudos.

[Anonymous]

Sure,

The input is a simple table for this example but giving you data that would get the result might be tough.  I can put some together and link you a google sheet if needed.

The parts of the table were interested in simply looks like

Submit Date     Product

01/01/2020      windows

01/01/2020      windows

01/01/2020      excel

01/01/2020      windows

01/01/2020      windows

01/01/2020      outlook

I have a measure called created which simply counts how many entries there are.

I've used that to make a table visulisation which looks like the below minus the end column, which I'm trying to get to.

                     Created     % of total in last 30 days

Product 1      1000         10%

Product 2      500           5%

Product 3      200           2%

I have a list of 100s of Products, which I'm filtering to top 10 in the last 30 days.  I want the end column to say what the % of the product is against the total volume (not the % of the top 10)

[v-yiruan-msft]

Hi @Anonymous ,

Assume that the measure [Created] has been created in your report, you can create a measure as below. If it is not applicable for your scenario, please provide your desired result with example. Thank you.

% of total in last 30 days = DIVIDE ( [Created], COUNTROWS ( ALL ( 'Table' ) ) )

Best Regards
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.

[Anonymous]

Thanks for that, very useful for a lot of my charts actually!  It's just missing one part for the this particular table.

I have 101k records in this dataset going back to the start of 2020.  This table is filtered to show only those with a created date in the last 30 days.  

The code you kindly provided takes the 875 created in the last 30 days (first yellow) and divides it by the full 101k rows.  I'm trying to get it to divide against the number of rows with a submit date in the last 30 days only - in this case 11,645 (third yellow).  And that will give me the % I'm after (second yellow)

[Anonymous]

@v-yiruan-msft  Sorry missed the tag in my previous reply

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

It's OK. Please update the formula of measure [% of total in last 30 days] as below and check whether it can get your desired result:

% of total in last 30 days = DIVIDE ( [Created], COUNTROWS ( ALLSELECTED ( 'Table' ) ) )

Best Regards
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.