Forum Discussion
Excluding Selected Data in Graphs
I guys, I am looking for some help with a rather specific issue I have been working on for the last couple days. I unforunately cannot link the Report as my company PC prevents this in any form.
I will make this as detailed as I can - apologies for the length.
It is important to note that this is a tiny slice of a huge report and changing the format of the data is practically out of the question, unforunately. It is done on the basis of anonymising and comparing data.
In this example with dummy data I have two tables:
Selection:
| Insurer Name | Insurer Flag | Peer & Market Choice | Insurer List | Market List |
| Insurer A | Market | Z_Insurer A | Insurer A | |
| Insurer B | Market | Z_Insurer B | Insurer B | |
| Insurer C | Market | Z_Insurer C | Insurer C | |
| Insurer D | Market | Z_Insurer D | Insurer D | |
| Insurer E | Market | Z_Insurer E | Insurer E | |
| Insurer F | Market | Z_Insurer F | Insurer F | |
| Insurer G | Market | Z_Insurer G | Insurer G | |
| Insurer H | Market | Z_Insurer H | Insurer H | |
| Insurer I | Market | Z_Insurer I | Insurer I | |
| Insurer A | Insurer | Insurer A | Insurer A | |
| Insurer B | Insurer | Insurer B | Insurer B | |
| Insurer C | Insurer | Insurer C | Insurer C | |
| Insurer D | Insurer | Insurer D | Insurer D | |
| Insurer E | Insurer | Insurer E | Insurer E | |
| Insurer F | Insurer | Insurer F | Insurer F | |
| Insurer G | Insurer | Insurer G | Insurer G | |
| Insurer H | Insurer | Insurer H | Insurer H | |
| Insurer I | Insurer | Insurer I | Insurer I |
This table is linked to my second table (by Peer & Market Choice), Data:
| Insurers | Frequency | GWP | SCR% | Insurer Flag | Peer & Market Choice | Branch |
| Insurer A | 3 | 638284 | 1.97 | Market | Z_Insurer A | Market |
| Insurer B | 0.14 | 496233 | 1.84 | Market | Z_Insurer B | Market |
| Insurer C | 6.86 | 604928 | 1.43 | Market | Z_Insurer C | Market |
| Insurer D | 5.52 | 897758 | 1.96 | Market | Z_Insurer D | Market |
| Insurer E | 9.13 | 259317 | 1.41 | Market | Z_Insurer E | Market |
| Insurer F | 4.06 | 150725 | 1.17 | Market | Z_Insurer F | Market |
| Insurer G | 7.22 | 611947 | 1.27 | Market | Z_Insurer G | Market |
| Insurer H | 2.63 | 765284 | 1.53 | Market | Z_Insurer H | Market |
| Insurer I | 6.16 | 181607 | 1.89 | Market | Z_Insurer I | Market |
| Insurer A | 3 | 638284 | 1.97 | Insurer | Insurer A | Insurer A |
| Insurer B | 0.14 | 496233 | 1.84 | Insurer | Insurer B | Insurer B |
| Insurer C | 6.86 | 604928 | 1.43 | Insurer | Insurer C | Insurer C |
| Insurer D | 5.52 | 897758 | 1.96 | Insurer | Insurer D | Insurer D |
| Insurer E | 9.13 | 259317 | 1.41 | Insurer | Insurer E | Insurer E |
| Insurer F | 4.06 | 150725 | 1.17 | Insurer | Insurer F | Insurer F |
| Insurer G | 7.22 | 611947 | 1.27 | Insurer | Insurer G | Insurer G |
| Insurer H | 2.63 | 765284 | 1.53 | Insurer | Insurer H | Insurer H |
| Insurer I | 6.16 | 181607 | 1.89 | Insurer | Insurer I | Insurer I |
Please do feel free to copy this data and create your own Report as I have described above, and also using the included pictures below for reference. Note: Branch is used in the legend to separate out the Market and Insurer data.
I have two slicers on the Selection Table which select the desired Insurer and Market companies. The issue arrises when I need to include the same company selection from my Insurer List slicer as in the Market List slicer (this is for a number of calculations that will need to include the Insurer List selections in the selected Market List), but I am trying to avoid this same data coming through just on the graphs. You can see this in Figure 1 - in the bar graph, where there is the identical Insurer A data column coming through. It is less visible but still occuring in the scatter graph where the Market datapoint is positioned above the Insurer A point rendering it invisible.
Figure 1:
The graphs in their current state. Note the duplication of data.
Figure 2 is what I am aiming for with the visuals (I have achieved this temporarily by just deselecting Insurer A), but this will not include Insurer A in aggregated Market data anymore - which I need to include.
Figure 2:
How I want the graph to look without having to unselect Insurer A. (I need A selected for many other calculations for all Market selections)
I have made a measure called DoubleCheck:
DoubleCheck = SWITCH(SELECTEDVALUE('Selection'[Insurer List]) = SELECTEDVALUE('Selection'[Market List]), TRUE(), "Dupe", "Single")As you can see from Figure 1, 2, this has not worked how I wanted. I was looking to make a measure that will help identify that Insurer A occurs in the selection for Insurer List and Market List. Ideally it would read "Dupe" for both Insurer A rows.
I made another (overly) complicated measure which does not work as I want it and gives a result like Figure 1:
Desired Frequency =
VAR SelectedInsurers = VALUES('Selection'[Insurer List])
VAR SelectedMarket = VALUES('Selection'[Market List])
VAR InsurerInMarket = CALCULATE(SWITCH(SELECTEDVALUE('Selection'[Insurer List]) = SELECTEDVALUE('Selection'[Market List]), TRUE(), "Single", "Dupe"), FILTER('Selection', NOT(ISBLANK('Selection'[Insurer List]))))
VAR InsurerData =
AVERAGEX(
FILTER(
'Data',
'Data'[Peer & Market Choice] IN SelectedInsurers
&& LEFT('Data'[Peer & Market Choice], 2) <> "Z_"
),
'Data'[Frequency]
)
VAR MarketData =
CALCULATE(
AVERAGE('Data'[Frequency]),
FILTER(
'Selection',
InsurerInMarket = "Single" &&
NOT(
ISBLANK(
'Selection'[Market List]
)
)
)
)
RETURN
InsurerData + MarketData
Thanks if you made it this far reading through.
2 Replies
- amitchandakSuper User
Anonymous , Try like
Desired Frequency =
VAR SelectedInsurer = SELECTEDVALUE('Selection'[Insurer List])
VAR SelectedMarket = SELECTEDVALUE('Selection'[Market List])VAR InsurerInMarket =
IF(
ISBLANK(SelectedMarket),
BLANK(),
IF(
CALCULATE(COUNTROWS('Selection'), FILTER('Selection', 'Selection'[Insurer List] = SelectedInsurer && 'Selection'[Market List] = SelectedMarket)) > 0,
"Dupe",
"Single"
)
)VAR InsurerData =
IF(
InsurerInMarket = "Dupe",
BLANK(),
CALCULATE(AVERAGE('Data'[Frequency]), 'Data'[Peer & Market Choice] = SelectedInsurer)
)VAR MarketData =
IF(
InsurerInMarket = "Single",
CALCULATE(AVERAGE('Data'[Frequency]), 'Data'[Peer & Market Choice] = SelectedMarket),
BLANK()
)RETURN
InsurerData + MarketData- AnonymousNot applicable
Thank you amitchandak for your reply. I can see how this would work but currently I cannot get it to correctly identify what is selected in both slicers and what is not. I believe the issue is within here somewhere:
VAR InsurerInMarket = IF( ISBLANK(SelectedMarket), BLANK(), IF( CALCULATE(COUNTROWS('OneApp Selection'), FILTER('OneApp Selection', 'OneApp Selection'[Insurer List] = SelectedInsurer && 'OneApp Selection'[Market List] = SelectedMarket)) > 0, "Dupe", "Single" ) )