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"
This will certainly require quite a bit of effort to implement for all the different visuals that are present in the full report, but I can attest that it does work! Thank you.
- bcdobbs4 years agoCommunity Champion
Do you have lots of different measures that would need the same treatment?
If so it might be worth seeing if you can use a calculation group to enable the logic to be reused easily.