Forum Discussion

Frequent Visitor

3 years ago

Solved

Dynamic Group By Table.

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 ...

Anonymous

3 years ago

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

Anonymous

Not applicable

3 years ago

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
Frequent Visitor
3 years ago
Hey Anonymous ,
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
- Anonymous
  Not applicable
  3 years ago
  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