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...
Need Help
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 ,

 

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

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

      Hi Anonymous ,

       

      Yes, all values returned  1.

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

       

      Thanks,

      Rich