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