Occurrence %

[Anonymous]

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_id	Date
1	123	5/5/2021
2	123	5/5/2021
1	321	5/5/2021
2	321	5/5/2021
3	321	5/5/2021
2	213	5/10/2021
3	213	5/10/2021

what I'm trying to do is count the % of total where Item 1 and 2 show up, while filtering 3 off the visual with a filter and having a date slicer on the report.  I'm trying the following dax as I want to count the total times the products show up 7 vs the count of the indivual products (example product 1) 2  to get a % of occorance the product shows up on orders. 

total products=

CALCULATE(

COUNT([Product])

, ALLEXCEPT([Date])

)

this dax only counts the total times the products show up for item 1 and 2, I want to show for items 1,2, and 3 with 3 not shown 

Ideal output would look something like this

Product	times present	total items	Occurrence
1	2	7	28.5%
2	3	7	42.8%
3 is filtered out and not visible

[jmalone]

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]

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 · Accepted solution]

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]

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]

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] )