PowerBI Bin Feature not giving the same result as Excel FREQUENCY()

[carlomendoza]

I just tried out the group bin feature in PowerBI desktop, but it seems it doesn't behave the same way as Excel's FREQUENCY() function. See picture. Is there a way make it match how FREQUENCY works? Don't mind the colors and bar widths. I'm talking about the values of the bars being different.

[carlomendoza]

Are there new features that make this easier?

[tahmed]

 Yes, now its much easier. PowerBI has a "group" feature that you can use. Simply right click on the Field and select New Group and you can do it that way. 

[v-yulgu-msft · Accepted solution]

Hi @carlomendoza,

The Group bin feature in PowerBI desktop is not the same as that in Excel. It only supports specific interval (bin size) that displayed in x-axis. For example, the x-axis can show value like 0,5,10,15,etc, but cannot show values as 0,5,6,8,9,etc. which is supported in Excel. That is the reason why the values of the bars are different.

In PowerBI, you can try the workaround below. First, you need a table with bin data and add an index column (in query edit) to it. Use the following formula to add an calculate column:

BinStart =
SUMX (
    FILTER ( BinTable, BinTable[index2] = EARLIER ( BinTable[index2] ) - 1 ),
    BinTable[BinEnd]
)

           

Then, cross join the Bin table with the table which contains the value columns need to be count. Create a calculate table based on that crossjoin table: 

CrossJoin = CROSSJOIN(Bin,BinTable)

NewTable =
CALCULATETABLE (
    'CrossJoin',
    FILTER (
        'CrossJoin',
        'CrossJoin'[Values] > 'CrossJoin'[BinStart]
            && 'CrossJoin'[Values] <= 'CrossJoin'[BinEnd]
    )
)

column = NewTable[BinStart]&"~"&NewTable[BinEnd]

Then, use the last table view to create bar chart. 

Thanks,
Yuliana Gu

[tahmed]

Hi,

I just found a much easier and faster way to do the same thing.  All you have to do is go into edit queries and add a conditional column.  Then start from the largest bin and start adding conditions "greater than" next interval.  See full example here:  http://exceleratorbi.com.au/conditional-columns-power-bi-desktop/

After you add the conditional column, you can then create visuals on that conditional column.  

[tahmed]

KUDOS!!  It does work, but It takes a loonnggg time to follow & create 3 tables just to put the counts into bins!  Wow, excel is much easier, just one simple 10-sec function called "Frequency".  Power BI needs to do something about that!  But thanks so much for your help!