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

JaySharma's avatar
JaySharma
Frequent Visitor
3 years ago

Hello Anonymous , 
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

 

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
    JaySharma
    Frequent Visitor
    3 years ago

    Hey Anonymous ,

    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