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.

     

     

jmalone's avatar
jmalone
Resolver III
5 years ago

It sounds like you want the % of orders that had Product 1 and/or Product 2? 

DIVIDE ( 
   CALCULATE ( 
       DISTINCTCOUNT( 'TableName'[Order_id] ),
       'TableName'[Product] IN { "1", "2" )
    ),
    DISTINCTCOUNT( 'TableName'[Order_id] )
)
  • 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] )

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    I think I'm asking this wrong, I need the total number of rows with the filtered out "3" product which is 7

    then I need the total number of rows for each "1" and "2", which would be 2 and 3

     

    then I would just divide the Occurrence of "1" by the total to get 28.5% of the time "1" product shows on an order

     

    • jmalone's avatar
      jmalone
      Resolver III
      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.