Displaying ISINSCOPE

[Reigning_data]

Hi all,

I have an issue which I will try to explain as best I can.

Companies A, B and C all sell various types of computers and desks. However, Comapny A does NOT sell standing desks, while B and C do.

Products have been categorised into 3 types. Computers, Desks, and Standing desks.

I want to see the % share of the total value for each company, and I used this code to do so.

Market Share = VAR __BASE_VALUE = 
                CALCULATE([total value])
VAR __TOTAL_VALUE =
                CALCULATE([total value],ALL(Sheet1[Company]))
RETURN
               CALCULATE( IF(
                                NOT ISBLANK(__BASE_VALUE),
                                DIVIDE(__BASE_VALUE, __TOTAL_VALUE)),ALL(Sheet1[Company]))

where total value is 

total value = CALCULATE(SUM(Sheet1[value]))

When I put a slicer into the report for Product type I can see the share each company has for that product type - PERFECT.

The problem arises when I want to see company A's share % INCLUDING standing desks. Because company A does not sell standing desks, it appears to be filtered out completely, and shows the % of just Desk and Computers

So I used ISINSCOPE to see if I could work around it like so:

ALL SELECTED Company = 
    CALCULATE([total value],
        ALLSELECTED(Sheet1[Company])
        )

ALL SELECTED Product Type = 
    CALCULATE([total value],
        ALLSELECTED(Sheet1[Product Type])
        )

ISINSCOPE = 
SWITCH (
    TRUE (),
    ISINSCOPE (Sheet1[Company]),DIVIDE([total value],[ALL SELECTED Company])
    ,
    ISINSCOPE ( Sheet1[Company] ), DIVIDE([total value],[ALL SELECTED Product Type]
    )
)

With this, the % shows perfectly...but only in a table, and only while nothing is selected on the page. Selecting something will change it to 100% and what I really need is to be able to display the % as a card of some kind.

Any ideas? I'm all ears!

[Greg_Deckler]

Sample data would be immensely helpful. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Reigning_data]

For reference, This is my results with slicers all selected.

[AkhilAshok]

Why not just trying to explicitly consider all products while calculating the Market Share for a Company?

Market Share =
DIVIDE (
    [Total Value],
    CALCULATE (
        [Total Value],
        ALL ( 'Table'[Company] ),
        VALUES ( 'Table'[Product Type] )
    )
)

[Reigning_data]

Hi AkhilAshok,

Many thanks for your response.

Unfortunately this still does not give the desired results.

See below (renamed to Market Share 2.0)

 

Market share when highlighting Company A and all 3 slicers selected should be 32.6%

[AkhilAshok]

Well, then I misunderstood your requirment. If that is the case, then I'm curious why your original code didn't work? Below code should give 32.6%

Market Share =
DIVIDE ( [Total Value], CALCULATE ( [Total Value], ALL ( 'Table'[Company] ) ) )

[Reigning_data]

Hi AkhilAshok,

I'm still getting 36% from that code in the same scenario?

To be clear, if the slicer is completely deselected, the correct 32.6% shows. But with all three selected, it shows 36%.

I need to select all three because in the real data, I have more than those 3 slicers.

Hopefully that makes sense

Thanks again for your response.

[AkhilAshok · Accepted solution]

You can easily solve the issue by converting you model to a Star Schema. 

1. Create 2 new tables as below:

Company = DISTINCT('Table'[Company])

Product = DISTINCT('Table'[Product Type])

2. Create 2 new relationships:

Table[Company] <- Company[Company]

Table[Product Type] <- Product[Product Type]

3. Now replace the dimensions in your existing visuals & slicers with the columns from the new Lookup tables.

4. Just use the same old measure for Market Share:

Market Share =
DIVIDE ( [Total Value], CALCULATE ( [Total Value], ALL ( 'Table'[Company] ) ) )

The issue you faced is happening because of the Auto Exists feature in DAX.

[Reigning_data]

You, Sir

are magnificent!

[Reigning_data]

Thanks for your reply,

Yes apologies, i had actually thrown a pbix together but could not find a way to share it through the forum.

Hopefully this will work.

Many thanks!

Company	Product Type	value
Company A	Computer	£    99.49
Company B	Computer	£    99.11
Company C	Computer	£    99.09
Company A	Desk	£    98.39
Company A	Desk	£    91.92
Company C	Standing Desk	£    89.26
Company A	Computer	£    89.09
Company B	Desk	£    88.50
Company B	Computer	£    86.87
Company C	Computer	£    85.46
Company A	Computer	£    84.91
Company B	Standing Desk	£    84.58
Company C	Computer	£    84.28
Company C	Desk	£    84.04
Company A	Computer	£    83.82
Company A	Computer	£    82.35
Company B	Desk	£    81.96
Company A	Computer	£    81.42
Company B	Standing Desk	£    79.92
Company B	Computer	£    77.98
Company B	Desk	£    72.47
Company B	Computer	£    72.34
Company A	Computer	£    69.99
Company A	Computer	£    67.95
Company C	Desk	£    66.86
Company C	Computer	£    66.10
Company A	Computer	£    65.61
Company C	Desk	£    64.32
Company B	Computer	£    64.00
Company A	Desk	£    63.92
Company C	Desk	£    63.64
Company B	Computer	£    63.46
Company C	Standing Desk	£    61.59
Company A	Desk	£    61.20
Company B	Standing Desk	£    58.85
Company C	Computer	£    58.44
Company C	Desk	£    57.79
Company B	Computer	£    56.93
Company B	Desk	£    56.72
Company B	Desk	£    56.31
Company B	Computer	£    54.56
Company B	Desk	£    54.18
Company B	Desk	£    53.52
Company A	Computer	£    53.12
Company B	Desk	£    52.44
Company B	Desk	£    51.86
Company A	Computer	£    51.44
Company A	Desk	£    51.33
Company C	Computer	£    50.89
Company B	Computer	£    50.79
Company B	Computer	£    50.64
Company A	Computer	£    49.93
Company A	Computer	£    49.16
Company C	Desk	£    48.54
Company C	Computer	£    47.44
Company A	Desk	£    44.70
Company C	Desk	£    42.32
Company C	Desk	£    41.56
Company A	Computer	£    41.20
Company A	Computer	£    40.63
Company A	Computer	£    39.63
Company C	Computer	£    39.60
Company C	Standing Desk	£    39.57
Company C	Desk	£    38.47
Company B	Standing Desk	£    37.10
Company B	Desk	£    36.94
Company C	Desk	£    36.80
Company C	Computer	£    36.52
Company B	Desk	£    35.36
Company C	Computer	£    34.53
Company B	Desk	£    33.82
Company A	Desk	£    33.69
Company B	Computer	£    32.81
Company C	Desk	£    31.18
Company C	Desk	£    29.11
Company C	Desk	£    28.58
Company C	Computer	£    27.68
Company C	Standing Desk	£    27.11
Company C	Desk	£    26.78
Company C	Desk	£    26.31
Company C	Desk	£    25.62
Company A	Computer	£    23.57
Company C	Computer	£    21.56
Company A	Desk	£    21.39
Company B	Computer	£    21.24
Company C	Desk	£    20.56
Company A	Desk	£    18.73
Company A	Desk	£    17.65
Company B	Desk	£    17.02
Company A	Computer	£    16.55
Company A	Desk	£    16.09
Company C	Desk	£    15.17
Company B	Computer	£    14.73
Company A	Computer	£    14.32
Company C	Computer	£    13.74
Company B	Desk	£    13.45
Company A	Desk	£    13.43
Company A	Computer	£    12.27
Company B	Computer	£    11.57
Company C	Desk	£    11.01
Company B	Computer	£    10.37
Company C	Desk	£    10.04
Company B	Computer	£       9.39
Company C	Computer	£       7.17
Company B	Desk	£       6.73
Company A	Computer	£       6.41
Company B	Computer	£       4.95
Company C	Computer	£       2.80