Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Occurrence %

having troubles getting a dax to work correctly   I have a single table with products, and unique order id's (can contain mutiple products) and date.    something like this   Product Order_...
Need Help
  • jmalone's avatar
    jmalone
    5 years ago

    You can modify the measure above to change the aggregation from distinctcount of order_ids to rows if you'd like. You can also modify the filter section as needed.

    For example, the following measure would give you Occurrence of Product 1:

    Occurrence of Product 1 =
DIVIDE ( 
   CALCULATE ( 
       COUNTROWS( 'TableName'),
       'TableName'[Product] = "1"
    ),
    CALCULATE ( 
       COUNTROWS( 'TableName'),
       ALL('TableName'[Product])
    )
)

     

    You could also write a measure to calculate the product occurrence dynamically:

    Occurrence of Selected Product =
DIVIDE (  
    COUNTROWS( 'TableName')
     ,
    CALCULATE ( 
       COUNTROWS( 'TableName'),
       ALL('TableName'[Product])
    )
)

    If you use the [Occurrence of Selected Product] measure in a visual, with the Product field in the rows/axis, the formula will return the occurrence % for each product. I think this is what you are asking for.

     

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Thanks Jmalone, what if I had say 200 products and wanted to manually filter those down to 4-5 could I use a paramater in that case in the "in" statement?

jmalone's avatar
jmalone
Resolver III
5 years ago

You could write out each of the products you want to include inside the IN statement, yes. This would work if you only have a handful of products. 

 

If you have many products that you want to include, you can store those as a separate table in your model (use the "Enter Data" feature in Power BI, or import a table from Excel, etc.), and use the following syntax, where ProductsToInclude is the name of the column:

'TableName'[Product] IN VALUES ( 'ManuallyCreatedTableName'[ProductsToInclude] )