Search code examples
powerbidaxmeasure

Count of products where sum of sales for particular product is higher than 2% of total sales - Power BI - DAX Measure

I have facts tables with sales

enter image description here

I would like to create measure that counts number of products where sum of sales for particular product is higher than 2% of total sales. For example: 1. sum of sales for 'ProductKey' 310 is 5000. 2. sum of sales for 'ProductKey' 346 is 2000. 3. 2% of sum of total sales is 3000. Product 310 would be included in count product 346 wouldn't be included in count. How would I write such measure? I've tried to created something like this:

Big Sales =  
var SalesTotal = CALCULATE(SUM(fact_InternetSales[SalesAmount]))    
var twoperceSalesAmount =
CALCULATE (SUM(fact_InternetSales[SalesAmount]), ALL( fact_InternetSales )) * 0.02
return
CALCULATE (COUNT(fact_InternetSales[ProductKey]),
FILTER (fact_InternetSales, SalesTotal - twoperceSalesAmount > 0))

Thansk

Kind regards, Robert

Solution

  • Also solution with SUMX works:

    Big Sales SUMX = 
SUMX(VALUES(fact_InternetSales[ProductKey]),
IF(CALCULATE(SUM(fact_InternetSales[SalesAmount]))> 
CALCULATE(SUM(fact_InternetSales[SalesAmount]),ALL(fact_InternetSales))*0.02,1))