Forum Discussion

TonyAshton's avatar
TonyAshton
Frequent Visitor
3 years ago
Solved

DAX help requied

I need help with creating a measure for the percentage of products with more than 1000 sales.

 

The table I have is called products which is a list of sales with each row a unique sale. Within this table I have a column called Product Name.

 

I have created a measure for total sales which is the count of site product name. But the measure I need is a little beyond me at the moment.

As I can’t work out in the one measure how to combine the following. 
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

DAX
power bi
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi TonyAshton 

    try like:

    BestProduct％ =
    VAR _allrproduct = 
    COUNTROWS(ALL(TableName[Product Name]))
    VAR _bestproduct =
    COUNTROWS(
        FILTER(
            VALUES(TableName[Product Name]),
            CALCULATE(COUNTROWS(TableName))>=1000
        )
    )
    RETURN
    DIVIDE(_bestproduct, _allrproduct)

2 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi TonyAshton 

    try like:

    BestProduct％ =
    VAR _allrproduct = 
    COUNTROWS(ALL(TableName[Product Name]))
    VAR _bestproduct =
    COUNTROWS(
        FILTER(
            VALUES(TableName[Product Name]),
            CALCULATE(COUNTROWS(TableName))>=1000
        )
    )
    RETURN
    DIVIDE(_bestproduct, _allrproduct)