Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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 |
Solved! Go to 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.
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] )
)
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
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.
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?
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] )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |