Forum Discussion

RichJW's avatar
RichJW
Icon for Helper III rankHelper III
4 years ago

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

Need Help

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      RichJW
      Icon for Helper III rankHelper III
      4 years ago

      Hi Anonymous ,

       

      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

    • RichJW's avatar
      RichJW
      Icon for Helper III rankHelper III
      4 years ago

      Hi Anonymous ,

       

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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