CODEWITHSUNDEEP
powerbidax
Tony Ashton
Tony Ashton

Reputation: 1

Power BI dax measure issue

I am having a challenge to write a measure for the following.

I have a table called products and each row is a different sales. I can work out the number of sales for each product but my challenge is the following.

From this table I am after a measure that will tell me from this table the % products with more than 1000 sales.

Really confused on how I do the following in a measure.

  1. Number of products
  2. Number of sales per product
  3. Number of products with more than 1000 sales
  4. % of products with more than 1000 sales

I have tried the following that returns a (blank) value.

% products Plus 1000 sales =
CALCULATE (
    DISTINCTCOUNT ( 'Products'[PRODUCT NAME] ),
    FILTER ( 'Products', [Total Sales] > 1000 )
)

Total sales = COUNT(‘Products’[PRODUCT NAME])

Upvotes: 0

Views: 178

Answers (2)

David Talbot
David Talbot

Reputation: 1

Number Of Products = DISTINCTCOUNT('Products’[PRODUCT NAME])




Number Of Products with High Sales = 
    CALCULATE(
        DISTINCTCOUNT('Products’[PRODUCT NAME),
        SUM('Products’, [TOTAL SALES])>1000)
)


  
% = DIVIDE([Number Of Products with High Sales],[Number of Products])

Upvotes: 0

Marcus
Marcus

Reputation: 4005

Here are some measures.

First off we write a measure that counts sales. From your attempts it looks like one row in the Product table is one sale. So to count sales we just need to count rows.

Total Sales = 
COUNTROWS ( 'Product' )

Then we make a simple measure that calculates the number of products. Apparently you have duplicates in your product table, so you have used DISTINCTCOUNT, which is fine:

# Products = 
DISTINCTCOUNT ( 'Products'[PRODUCT NAME] )

After this, we can make a third measure that counts products with more than 1000 sales. We reference the measure we have written previously here, and use a trick with IF within the COUNTX iterator to count. (For the curious, I tried COUNTAX with just the predicate, but COUNTAX counts both true and false - I would expect a false to be skipped, but that is from spending too much time in Python lately!)

Since the count of products is an aggregate, we also need to calculate the number of sales from a similar starting point, which is why we feed the iterator with a one-column table with VALUES that contain the distinct product names.

# Products with Sales > 1000 = 
COUNTX ( 
    VALUES ( 'Products'[PRODUCT NAME] ), 
    IF ( [Total Sales] > 1000 , 1 )
)

At this point, the final calculation is trivial, since we just invoke the previously defined measures within a DIVIDE:

% of Products with Sales > 1000 = 
DIVIDE ( 
    [# Products with Sales > 1000], 
    [# Products]
)

Upvotes: 0

Related Questions