Dynamic Group By Table.

[JaySharma]

I need to create a dynamic table grouped/binned dynamically with user inputs (Slicers). The Data looks like this. 

Index	Net Worth	Citizen
2	1000000	0
4	7100000	1
7	5900000	0
9	1800000	0
10	9500000	1
11	1500000	0
13	5700000	0
14	6400000	0
15	9900000	1
17	8300000	0
18	7800000	0
19	2200000	0
20	1000000	0

I have tried calculated tables, and creating various measures, but being a DAX beginner, I'm unable to solve the problem. 
This is how my input is taken. 

The expected output is somewhat like this.

Bins	Average NW	Count Index	Count Citizen
0-1938062
1938062-6623377
6623377-10000000

The bins need to be created on the basis of the slicer values. 

It'd be great if someone guides me towards the right approach. 

[JaySharma]

Hello @v-yiruan-msft , 
I added the 'Bins'[Index] in the visual before, and I believe text wrap has any effect on the values of 'Average NW', and the counts. 
Here's the screenshot: 

The expected output is in the original post, I was able to achieve it with a makeshift solution by creating measures for every Bin but I wanted all the values to be in a single table/visual. Here's the screenshot of something close to what I'm trying to achieve: 

Also, it'd be great if you could share your pbix file as for some reason, my tables aren't populating even when I did exactly what you did. 

Thanks & regards

Jay Sharma

[v-yiruan-msft]

Hi @JaySharma ,

I attached the pbix file here, please find the details in it. Please update the max value of parameter 'Slicer 1' and 'Slicer 2' as 10000000 after you got the pbix file. I update their value as 1000 in my attached pbix file due to the community upload file size limitation.

If it is convenient, you can also share your pbix file(exclude sensitive info) with me. Later I can check why the avg and count measures not return the values....

How to upload PBI in Community

Best Regards

[JaySharma]

Hey @v-yiruan-msft ,

The initial solution you provided, worked. The issue was with the data model, while trying something out previously, I connected the slicer table with the data table and that's why it wasn't populating. I apologize for the fruitless inconvenience I caused. 

Once I removed the useless relations between the tables, everything worked like a charm! 

Thanks a ton for helping me out !!!

Regards
Jay Sharma

[v-yiruan-msft · Accepted solution]

Hi @JaySharma ,

You can follow the steps below to get it, please check if that is what you want.

1. Create a dimension table using "Enter data" method

2. Create the measures as below

Bins = 
    SWITCH (
        SELECTEDVALUE ( 'Bins'[Index] ),
        0, "0-" & [NW-low],
        1,
            [NW-low] & "-" & [NW-high],
        2,
            [NW-high] & "-" & 10000000
    )

Average NW = 
VAR _len1 =
    SEARCH ( "-", [Bins], 1, 0 )
VAR _minnw =
    VALUE ( MID ( [Bins], 1, _len1 - 1 ) )
VAR _maxnw =
    VALUE ( MID ( [Bins], _len1 + 1, LEN ( [Bins] ) - _len1 ) )
RETURN
    CALCULATE (
        AVERAGE ( 'Table'[Net Worth] ),
        FILTER ( 'Table', 'Table'[Net Worth] >= _minnw && 'Table'[Net Worth] <= _maxnw )
    )

Count Index = 
VAR _len1 =
    SEARCH ( "-", [Bins], 1, 0 )
VAR _minnw =
    VALUE ( MID ( [Bins], 1, _len1 - 1 ) )
VAR _maxnw =
    VALUE ( MID ( [Bins], _len1 + 1, LEN ( [Bins] ) - _len1 ) )
RETURN
    CALCULATE (
        COUNT ( 'Table'[Index] ),
        FILTER ( 'Table', 'Table'[Net Worth] >= _minnw && 'Table'[Net Worth] <= _maxnw )
    )

Count Citizen = 
VAR _len1 =
    SEARCH ( "-", [Bins], 1, 0 )
VAR _minnw =
    VALUE ( MID ( [Bins], 1, _len1 - 1 ) )
VAR _maxnw =
    VALUE ( MID ( [Bins], _len1 + 1, LEN ( [Bins] ) - _len1 ) )
RETURN
    CALCULATE (
        SUM ( 'Table'[Citizen] ),
        FILTER ( 'Table', 'Table'[Net Worth] >= _minnw && 'Table'[Net Worth] <= _maxnw )
    )

3. Create a table visual which need to include the field [Index] of dimension table 'Bins'

Best Regards

[JaySharma]

Hey @v-yiruan-msft ,
Thank you for responding!

I did exactly what you mentioned, but the average and count fields aren't getting populated. 

To check where is it broken, I Replaced the return variable so that I can verify every variable is returning the right value. 

[Bins] seem fine.

So does the _len1

_minnw and _maxnw are correct too. 

This is where it breaks down: 

I hope we find a solution to this problem. 

Regards, 

Jay Sharma

[v-yiruan-msft]

Hi @JaySharma ,

Actually, I also add the field [Index] of the table 'Bins' in the table visual. But it was "hidden" by toggling off the option "Text wrap" of Values & Column headers and reducing the size of field [Index].

Toggle off the option "Text wrap"

Base on your provided data, what's your expected result? Could you please explain more details with the specific examples or screenshots? It would be helpful to find out the solution. Thank you.

Best Regards