Forum Discussion

JaySharma's avatar
JaySharma
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's avatar
    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