Forum Discussion
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!
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.
9 Replies
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
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 For reference, This is my results with slicers all selected.
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] ) ) )
