Splitting tables on different tabs by %

[RichJW]

Hi,

I’m using Power BI Desktop and I am having difficulty in splitting 2 tabs of data equally.

In summary…

One tab has a table of data, which can have 30+ entries (rows) in the table. As the table I am using only has room for approx. 20 rows, I need to create a second tab in the report.

However, I need the report to split the content between the 2 tabs equally. So, if the first tab would have 32 entries, I need them both split into 2 tabs x 16 entries. If it had 18 entries, it would have 9 each – you get the point. Both tabs would be using the same columns.

Using column filters won’t work, as the data is changeable and what looks like a 50/50 split now, might not be next week.

Ideally, I would like my column name of “Project Name” used alphabetically to put the top 50% of the data in the first tab and the bottom 50% in the second. As the “Filter type” “Top N” does not allow me to use percentages, I can’t seem able to do this.

Can anyone help?

Thanks all!

Rich

[v-jayw-msft]

Hi @RichJW ,

Create a measure like below and add it to visual filter. Visual_1 set value = 1, Visual_2 set value = 0.

Measure = 
var _index = CALCULATE(COUNTROWS('Table'),'Table'[id]<=MAX('Table'[id]))
var _mid = ROUNDDOWN(COUNTROWS(ALLSELECTED('Table'[id]))/2,0)
return
IF(_index<=_mid,1,0)

Best Regards,

Jay

[RichJW]

Hi @v-jayw-msft ,

I've copied across the above measure and edited the table/column to my reports, however it isn't working as expected. The first tab shows all the list (filtered for 1), whereas the second table )filtered for 0) shows no data. I receive no error, and have tried it with both text and numbered data but to no avail.

Have I missed something?

Thanks,

Rich

[v-jayw-msft]

Hi @RichJW ,

All values return 1?

You can try splitting this measure into the following measures to see what went wrong.

measure1 = CALCULATE(COUNTROWS('Table'),'Table'[id]<=MAX('Table'[id]))

This measure should return 1 for first row, 2 for second and so on.
measure2 = ROUNDDOWN(COUNTROWS(ALLSELECTED('Table'[id]))/2,0)

This measure should return half of the total number of records and rounded down.
Please check if both measures return the correct value.

Best Regards,

Jay

[RichJW]

Hi @v-jayw-msft ,

Yes, all values returned  1.

In the above split measures, the first measure produced "1" for all entries. The second produced "357"!

Thanks,

Rich

[RichJW]

Hi @v-jayw-msft ,

Thank you so much, that will help me immensly. I'll test it over the next few days and will accept as a solution if I can get it to work.

Cheers,

Rich

[amitchandak]

@RichJW , You need have count visual rows that you have using countrows summarize on all group by column and measure

and you can use rank filter two split on each page

Also check this pagination approach, if that can help

https://www.fourmoo.com/2017/10/31/power-bi-how-to-do-pagination-in-power-bi-reports/

[RichJW]

Thanks @amitchandak , I'll take a look.