Matrix values help

[Anonymous]

Hello - I have a straightforward problem and I'm struggling to find a solution. I have a matrix I'm building that reports accounting data on a project order level... the row hierarchy goes project name > project number > order number.The highlighted column, budget amount, is pulled from a different summary table for project number statistics. Thus, the entire project budget amount is populating on every row of the matrix (all of the individual order numbers), and I need it to populate on only the project number and project name rows.

Can someone point me in the right direction for a workaround for this? I'd like to report on the budget amount in the same matrix with the individual order number information. I have many more columns not pictured to the right that are calculating correctly, it's just the budget amount I'm having trouble with.

[amitchandak · Accepted solution]

@Anonymous ,Refer if this can help

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

[amitchandak · Accepted solution]

@Anonymous ,Refer if this can help

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

[parry2k]

@Anonymous you have to share your data model to get further help on this, share how you are tables are related and what columns you are using in the matrix, underline problem is the relationship between tables.

[Anonymous]

@parry2kthanks for the response. Here is a picture of the model: All tables are connected via the CER number, which is the project number. The values in my matrix are calculated columns from the Append 1 table, which brings in data on the Project Order Number level. Here's a screenshot of that table: What I am trying to fix in the original matrix visualization is the budget amount, which comes from the CAPEX Metrics table (table in between Projects and Append1 in the model). Here's an example of that table: still having trouble with this - my DAX skills aren't very advanced, but usually I can figure things out.

[parry2k · Accepted solution]

@Anonymous got it, use ISINSCOPE function to control where you want to show the budget amount, here is the post that talks about it in detail.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[jthomson]

You'd either need to:

a) make some sort of relationship between your tables so that $62k is distributed between the order lines

b) create a measure that returns a blank when something is filtered by an order line but returns the result when it doesn't

[Anonymous]

Thank you... I'll go with the measure. I couldn't figure out how to go about writing that measure but it is really clear the way you put it. Thanks