DAX Logic Based on Drill Down Values

[Anonymous]

Hi, 

I need to build DAX logic that has to be in a measure (Data Model is fixed, no calculated columns allowed) that uses the related values in different parts of the data.

The Drill down goes from: Customer-> Product Category-> Brand -> Individual Product. The logic will be: Distribution> 85% (this is already done for each product in a matrix with this drill down) and the second measure (X) has to be above a certain number for the products- this is category dependant. EG For the category Ice-Cream, the second measure X has to be >300 with a weighted distribution >85%, however for the category Sauces, X has to be above 450. 

If the product for that roll up (for a specific cust, category etc) fits the two criteria, it will output measure X for the individual product for a certain customer. 

-Measure X is static, it will always be a certain value for a category whereas the weighted distribution is too, but this value is only relevant given at the lowest level (individual product).

How would I go about writing this? Thank you!

[amitchandak]

@Anonymous , based on what I got initially isinscope can help

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

Thank you for your response 🙂 The aim is to pull the X measure and place into another table when Distribution> 85 and X>(a value), (value is different depending on which category that product is in). The table above shows the drill down. 

The output will be a table showing all products which hit these criteria and which customer they are in. EG product A maybe sold in customer G and K, but in G distribution is 70, K is 95 therefore we need to show product in G not K.