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

 

ProductOrder_idDate
11235/5/2021
21235/5/2021
13215/5/2021
23215/5/2021
33215/5/2021
22135/10/2021
32135/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

 

Producttimes presenttotal itemsOccurrence

1

2728.5%
23742.8%
3 is filtered out and not visible   
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.

     

     

5 Replies

  • 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.