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.

     

     

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.

      • AkhilAshok's avatar
        AkhilAshok
        Solution Sage
        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.