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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RichJW
Helper III
Helper III

Splitting tables on different tabs by %

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

7 REPLIES 7
v-jayw-msft
Community Support
Community Support

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)

1.jpg

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

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

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

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

 

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

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

Thanks @amitchandak , I'll take a look.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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