Forum Discussion
Dynamic Group By Table.
- Anonymous3 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
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