Forum Discussion

Reigning_data's avatar
Reigning_data
Frequent Visitor
7 years ago
Solved

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...
Need Help
  • AkhilAshok's avatar
    AkhilAshok
    7 years ago

    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's avatar
Reigning_data
Frequent Visitor
7 years ago

 

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

AkhilAshok's avatar
AkhilAshok
Solution Sage
7 years ago

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's avatar
    Reigning_data
    Frequent Visitor
    7 years ago

    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's avatar
      AkhilAshok
      Solution Sage
      7 years ago

      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's avatar
        Reigning_data
        Frequent Visitor
        7 years ago

        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.