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

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.