cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver II
Resolver II

Top80% sales from X units of companies

Hi DAX expert, Can you help with the DAX : How many companies contributed to the Top80% of Sales.. TopN.JPG

3 REPLIES 3
Highlighted
Resolver I
Resolver I

Re: Top80% sales from X units of companies

Hi @malcolmwun , because I always break down my measures into pieces to make it easier to write, read and understand, I'd write my measures like below, assuming your final result should be a number.

 

  1. Number of Companies = COUNT(YourTable[Customer Name])
  2. No of Companies above 80% = CALCULATE([Number of Companies], YourTable[Share Cumulative] > 0.8)

If this answers your question, kindly kudo and mark as solution to enable many more people gain from it.

Highlighted
Resolver II
Resolver II

Re: Top80% sales from X units of companies

how do i write the DAX if i do not have a Cumulative calculation column?

Highlighted
Super User IV
Super User IV

Re: Top80% sales from X units of companies

// Say you have:
// 1. Dimension Customer
// 2. Fact Sales
// and they're connected this way:
// Customer 1:* Sales
// This is somewhat standard.
// Apart from this there are maybe
// other dimensions connected to
// the fact table.
// You need to define the following:

[Sales] = sum(Sales[Sales])

// Idea: Go through all selected/visible customers
// and find their cumulative share relative
// to the all the selected/visible customers.
// Once you have the table, find the number of
// them with cumulative share <= 80%.

// Attention: This measure
// is to be used on its own. If you put
// it in a table that displays Customers
// one by one, you'll get a correct result
// but NOT what you expect. It means it'll
// calculate something different than what
// you think it should calculate.

// Assumption: Customer[Customer Name] is
// unique. If not, then [Customer Name]
// should be replaced with a unique identifier
// in the Customer dimension.

[#Cust Where Cumul Sales <= 80%] =
var __threshold = .8
var __totalSales = [Sales]
var __custsWithSales =
	addcolumns(
		values(Customer[Customer Name]),
		"CustSales", [Sales]
	)
var __custsCount =
	countrows(
		filter(
			__custsWithSales,
			var __currentSales = [CustSales]
			var __cummulativeSales =
				sumx(
					__custsWithSales,
					[CustSales] * ([CustSales] >= __currentSales)
				)
			return
				divide(__cummulativeSales, __totalSales) <= __threshold
		)
	)
return
	__custsCount

Please bear in mind that I've written this without actually running it because I don't have time to create a model and populate with data. You might need to adjust this but I think it should be OK. Let me know if it's not.

 

Best

D



Have I answered your question?
Please mark my post as the solution.


Please don't forget your Kudos if you don't mind. Thanks.


Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors