Measure - Market share of grand total

[depple]

Hi,

First of all, I am quite a newbie with creating Dax measures, so please bear over with my shortcomings of knowledge (and probably also ability to explain the actual issue).

Primarily, I am trying to visualize (line chart) the development of market share of a selected group of products, the base being sales of all products. 

Mockup of all data (no column for :

table1

Product	Type	Data level	Size	Sales	Share of Grand Total Sales
Alfa	A	Geography	S	10	0,10
Bravo	B	Geography	M	15	0,15
Charlie	B	Chain	L	25	0,25
Delta	C	Chain	M	40	0,40
Echo	B	Chain	M	10	0,10
Grand Total				100	1

So the key is: After slicer operations, the Sales of each product is to be measured against the "all" salesa in the table.

Desired result of measure (* mark slicer selections)

Product	Type*	Data level*	Size	Sales	Share of Grand Total Sales
Charlie	B	Chain	L	25	0,25
Echo	B	Chain	M	10	0,1
Total					0,35

My secondary challenge, due to the data structure, I need the Dax measure to control which of the slicers that should influence the base (and others not). Is this possible?

I would really appreciate any input.

Regards,

depple

[Fowmy · Accepted solution]

@depple 

You can create the following measure to get the desired results:

Share of Grand Total Sales = 
DIVIDE(
    SUM(SalesData[Sales]),
    CALCULATE(
            SUM(SalesData[Sales]),
            ALL(SalesData)
    )
)

If you need to control the clearing of filters then you can selectively choose with ALLEXCEPT as follows:

Share of Grand Total Sales = 
DIVIDE(
    SUM(SalesData[Sales]),
    CALCULATE(
            SUM(SalesData[Sales]),
            ALLEXCEPT(SalesData, SalesData[Type])
    )
)

[depple]

Hi @Fowmy 

That worked perfectly!

Ended up with quite a lot of ALLEXCEPT-columns, maybe there should be a ALLSELECTEDEXCEPT function 🙂

Thank you very much for your help, that really pushed my report forward.

/depple