Bin dynamically group by sets

[hernandezguzman]

Hi,

I would like to know if it is possible to create a bin chart where the bings are dinamic set by X number,

example if I have 1252 Customers and want to split them around 10  bings then each bin wouldbe group by (1252 / 10) = 125, but I change my date slicer then the customer total is now 1850 so the new bins will be group by (1850 / 10) = 185.

Is this possible to peform ,

I really appreaciate any help,

Regards,

[v-shex-msft]

Hi @hernandezguzman,

In fact, current power bi not able to create dynamic calculate columns based on filter/slicer.

You can try to create a measure based on the customs and slicers but it can't be used as the category/legend on your charts.
Regards,

Xiaoxin Sheng

[hernandezguzman]

thanks so much Xiaoxin Sheng for your answer, then to you consider it is not possible to create a bin chart that have always a maximun number of 10 bins and with equality quntity of customers on it ?

this is basically what I am trying to do,  I need to have a bin chart with not more than 10 bins bars and that the quantity of customers gets balaced distributed between the bins.

Thanks

Regards,

[v-shex-msft]

Hi @hernandezguzman,

The static version is possible, we can create calculated column formula to calculate the correct bin range based on table records.  The hard part is make it dynamic based on slicer/filter.

In fact, calculated column and filter/slicer are host on the different data levels, you can't use child level to effect its parent.

Notice: the data level of power bi.
Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view wiht virtual tables(measure, visual, filter, slicer)

Regards,

Xiaoxin Sheng

[hernandezguzman]

Hi  Xiaoxin Sheng, thanks so much, yes I found a video that does exactly What I am looking for, it does it as you say using calculated columns for static version, it is close to what I need, Only thing is I just need 10 bins but this solution add many more basses on the data set,

please see the video, maybe you can see somothing that makes the trick, or can finally make see that this is not possible

https://youtu.be/SFWHDWiqXzI

thank you in advance I am pretty knew at PBI and have struggled to get this exact as the client resquest, just need what the videos does but always 10 or less bin bars.

thanks so much

Regards 

but it makes the chart to increase the number of bins 

[v-shex-msft]

Hi @hernandezguzman,

So you only required the 'bin No' and 'bin range' fields mentioned in the video? If that is the case, can you please share some dummy data and expected results to test? It should help us test and coding formulas.

How to Get Your Question Answered Quickly 
Regards,

Xiaoxin Sheng

[hernandezguzman]

Hi, thanks so much for answering, I really appreciate it,
yes what I need is exact the same from the video but keeping always the same number of bins,

Please download the dummy example I cretated for you to check, it is a simple customer table and I applied the solution to balance the customer in same quantities, please notice that there is a date filter so when you select a week range it shows around 10 or less bins but if you increase the date range selection it increases the number of bins and that what I do not want, I want to keep always 10 or less bins so the only thing that should change is the quantity of customers in bin,
Please download it this link,
https://1drv.ms/u/s!AsQpVn9npEIt0Hwp7hS0dnSm0xx8?e=JhEcQO

Thanks so much
Regards,

[v-shex-msft · Accepted solution]

Hi @hernandezguzman,

I succeed to compress these fields and now you can just use two fields to get correspond values.

bin num = 
VAR _bin =
    SQRT ( COUNTROWS ( Customer ) - 1 ) - 1
VAR _size =
    CEILING (
        DIVIDE ( MAX ( Customer[CustomerId] ) - MIN ( Customer[CustomerId] ), _bin ),
        1
    )
RETURN
    FLOOR ( DIVIDE ( Customer[CustomerId], _size ), 1 ) + 1

Bin Names = 
VAR _bin =
    SQRT ( COUNTROWS ( Customer ) - 1 ) - 1
VAR _size =
    CEILING (
        DIVIDE ( MAX ( Customer[CustomerId] ) - MIN ( Customer[CustomerId] ), _bin ),
        1
    )
RETURN
     ( [bin num] - 1 ) * _size & " ~ " & [bin num] * _size

As I said, data view level slicer/filter are host ton the child table that generated from data model table which calculated columns/table host so you can't interact them to get dynamic results.
For this scenario, I'd like to suggest use query parameters, you can create two date type query aptamer and they allow you to input values.  You can create a blank query table to store the query parameters values.(let's named it as 'filter range' table)

Creating Tables In Power BI/Power Query M Code Using #table() 

After these stpes, you can create a calculated table to filter raw table records based on the 'filter range' table and add custom fields with calculated column expressions which I pasted above and you will get a table based on query parameter and dynamic bin ranges. (it will changes every time you modify the query parameter and apply changes)

Fitlered =
ADDCOLUMNS (
    ADDCOLUMNS (
        FILTER (
            Customer,
            [DateEntered] >= MIN ( 'Filter range'[Start] )
                && [DateEntered] <= MAX ( 'Filter range'[End] )
        ),
        "bin num",
            VAR _bin =
                SQRT ( COUNTROWS ( Customer ) - 1 ) - 1
            VAR _size =
                CEILING (
                    DIVIDE ( MAX ( Customer[CustomerId] ) - MIN ( Customer[CustomerId] ), _bin ),
                    1
                )
            RETURN
                FLOOR ( DIVIDE ( Customer[CustomerId], _size ), 1 ) + 1
    ),
    "Bin Names",
        VAR _bin =
            SQRT ( COUNTROWS ( Customer ) - 1 ) - 1
        VAR _size =
            CEILING (
                DIVIDE ( MAX ( Customer[CustomerId] ) - MIN ( Customer[CustomerId] ), _bin ),
                1
            )
        RETURN
             ( [bin num] - 1 ) * _size & " ~ " & [bin num] * _size
)

Regards,

Xiaoxin Sheng

[perezco]

hi @v-shex-msft , could you share the final file ? 

Regards, 

[hernandezguzman]

Hi Xiaoxin Sheng,

thanks so much, I really appreciate your help it has been so helpful, just last question, when you  say, query parameter, is it the same as direct query? I guess so, but want to be sure, I am clear that it cannot be dynamically based on child filters and this is the solution I will implement but just want to be sure about the query parameter = direct query

thanks so much for tanking the time and answer in susch a detail, really appreciate it

[v-shex-msft]

Hi @hernandezguzman,

Since query parameters are hosted on backend query tables, so modify the query parameters not directly change current tables and records.
For this scenario, you need to apply the change and refresh your report to force re-generate the data model tables based on changed parameters
Then calculate table will also change based on the latest version query parameters. (they not dynamic enough as live mode which changes immediately)

BTW, direct query mode has limited you to use DAX functions in calculated fields(column/table).

Regards,

Xiaoxin Sheng

[hernandezguzman]

Thanks so much Xiaoxin Sheng,

I am now clear about it and your answers helped me a lot, I appreciate all your patience and help,

Regards,