topic Re: Measure - Market share of grand total in DAX Commands and Tips

Measure - Market share of grand total

depple — Mon, 23 May 2022 12:17:40 GMT

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

Re: Measure - Market share of grand total

Fowmy — Mon, 23 May 2022 13:07:59 GMT

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]) ) )

Re: Measure - Market share of grand total

depple — Wed, 25 May 2022 06:45:07 GMT

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