cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Reigning_data Frequent Visitor
Frequent Visitor

Displaying ISINSCOPE

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
AkhilAshok Established Member
Established Member

Re: Displaying ISINSCOPE

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.

 

 

View solution in original post

9 REPLIES 9
Super User
Super User

Re: Displaying ISINSCOPE

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


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Reigning_data Frequent Visitor
Frequent Visitor

Re: Displaying ISINSCOPE

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!

 

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

Re: Displaying ISINSCOPE

Capture.JPG

 

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

AkhilAshok Established Member
Established Member

Re: Displaying ISINSCOPE

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 Frequent Visitor
Frequent Visitor

Re: Displaying ISINSCOPE

Hi AkhilAshok,

 

Many thanks for your response.

 

Unfortunately this still does not give the desired results.

 

See below (renamed to Market Share 2.0)

 Capture2.JPG

 

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

 

AkhilAshok Established Member
Established Member

Re: Displaying ISINSCOPE

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 Frequent Visitor
Frequent Visitor

Re: Displaying ISINSCOPE

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 Established Member
Established Member

Re: Displaying ISINSCOPE

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.

 

 

View solution in original post

Reigning_data Frequent Visitor
Frequent Visitor

Re: Displaying ISINSCOPE

You, Sir

 

are magnificent!

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)