Forum Discussion
Creating a Calculated Column Based on SELECTEDVALUE
- 4 years ago
1) Removed your existing inactive relationship and your calculated column.
2) Added a calculated table which lists each lender and adds an "Other Lenders" category:
Lender Grouped = UNION ( DISTINCT ( Lenders[Name] ), {"Other Lenders"} )3) Created a relationship to your main table (you need to force it to be 1 to many:
4) Created a basic total measure:
Lender Amount = SUM ( Lenders[Amount] )(This gets used in your normal ungrouped visual)
5) Created a measure to do the grouping:
Grouped Amount = VAR SelectedLenders = VALUES ( 'Lender Shell'[Name] ) VAR VisualLender = SELECTEDVALUE ( 'Lender Grouped'[Name] ) RETURN IF ( HASONEVALUE ('Lender Grouped'[Name]), SWITCH ( TRUE (), VisualLender IN SelectedLenders, [Lender Amount], VisualLender = "Other Lenders", CALCULATE ( [Lender Amount], NOT ('Lender Grouped'[Name] IN SelectedLenders ) ), BLANK() ), BLANK() )6) Swap both visuals over to use "Lender Grouped[Name]"
7) In the grouped visual replace the implicit measure with "Grouped Amount"
1) Removed your existing inactive relationship and your calculated column.
2) Added a calculated table which lists each lender and adds an "Other Lenders" category:
Lender Grouped =
UNION (
DISTINCT ( Lenders[Name] ),
{"Other Lenders"}
)3) Created a relationship to your main table (you need to force it to be 1 to many:
4) Created a basic total measure:
Lender Amount = SUM ( Lenders[Amount] )(This gets used in your normal ungrouped visual)
5) Created a measure to do the grouping:
Grouped Amount =
VAR SelectedLenders = VALUES ( 'Lender Shell'[Name] )
VAR VisualLender = SELECTEDVALUE ( 'Lender Grouped'[Name] )
RETURN
IF (
HASONEVALUE ('Lender Grouped'[Name]),
SWITCH (
TRUE (),
VisualLender IN SelectedLenders, [Lender Amount],
VisualLender = "Other Lenders",
CALCULATE (
[Lender Amount],
NOT ('Lender Grouped'[Name] IN SelectedLenders )
),
BLANK()
),
BLANK()
)
6) Swap both visuals over to use "Lender Grouped[Name]"
7) In the grouped visual replace the implicit measure with "Grouped Amount"
Hi Ben, your solution worked very well in a similar use case I had. Thank you so much for contributing this.