Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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 NameInsurer FlagPeer & Market ChoiceInsurer ListMarket List
Insurer AMarketZ_Insurer A Insurer A
Insurer BMarketZ_Insurer B Insurer B
Insurer CMarketZ_Insurer C Insurer C
Insurer DMarketZ_Insurer D Insurer D
Insurer EMarketZ_Insurer E Insurer E
Insurer FMarketZ_Insurer F Insurer F
Insurer GMarketZ_Insurer G Insurer G
Insurer HMarketZ_Insurer H Insurer H
Insurer IMarketZ_Insurer I Insurer I
Insurer AInsurerInsurer AInsurer A 
Insurer BInsurerInsurer BInsurer B 
Insurer CInsurerInsurer CInsurer C 
Insurer DInsurerInsurer DInsurer D 
Insurer EInsurerInsurer EInsurer E 
Insurer FInsurerInsurer FInsurer F 
Insurer GInsurerInsurer GInsurer G 
Insurer HInsurerInsurer HInsurer H 
Insurer IInsurerInsurer IInsurer I 

 

This table is linked to my second table (by Peer & Market Choice), Data:

InsurersFrequencyGWPSCR%Insurer FlagPeer & Market ChoiceBranch
Insurer A36382841.97MarketZ_Insurer AMarket
Insurer B0.144962331.84MarketZ_Insurer BMarket
Insurer C6.866049281.43MarketZ_Insurer CMarket
Insurer D5.528977581.96MarketZ_Insurer DMarket
Insurer E9.132593171.41MarketZ_Insurer EMarket
Insurer F4.061507251.17MarketZ_Insurer FMarket
Insurer G7.226119471.27MarketZ_Insurer GMarket
Insurer H2.637652841.53MarketZ_Insurer HMarket
Insurer I6.161816071.89MarketZ_Insurer IMarket
Insurer A36382841.97InsurerInsurer AInsurer A
Insurer B0.144962331.84InsurerInsurer BInsurer B
Insurer C6.866049281.43InsurerInsurer CInsurer C
Insurer D5.528977581.96InsurerInsurer DInsurer D
Insurer E9.132593171.41InsurerInsurer EInsurer E
Insurer F4.061507251.17InsurerInsurer FInsurer F
Insurer G7.226119471.27InsurerInsurer GInsurer G
Insurer H2.637652841.53InsurerInsurer HInsurer H
Insurer I6.161816071.89InsurerInsurer IInsurer 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

  • 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

    • Anonymous's avatar
      Anonymous
      Not 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"
              )
          )