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
Anonymous
Not applicable

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

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

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