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"
Sure thing, I've added it to dropbox here: https://www.dropbox.com/s/tk585qq3jy6nnzw/Lender%20Grouping%20Testing.pbix?dl=0
Let me know if I have to share it in some other way.
See what you make of this:
Possible Solution
I'll write up some explanation in a moment!
- bcdobbs4 years agoCommunity Champion
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"
- anaida4 years agoRegular Visitor
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.
- mellim1 year agoNew Member
Hi Ben, your solution worked very well in a similar use case I had. Thank you so much for contributing this.
- alecev1 year agoFrequent Visitor
I have a silimar problem I can't solve.
I need to select a specific Lender that must be showed with its name and its amount, but i don't want to "group" all the other Lenders in a single grouped amount, because I need to show all of them with their specific amounts, by "hiding" only their names.
Example, in case I select Lender2: