How to Combine Data from Tables with Different Granularity Levels for a Visual

[Jason_Walker]

Not sure if what I need is possible but I'd like to use data from multiple tables with different levels of granularity in a visual. Right now the tables are joined at the sub-category level but one table has data at a lower granularity level. When I add in a column at the sub-category level to the visual, the total sub-category amount is shown at each detail level.

Is there a way to have the sub-category column only show up in the visual at the sub-total level ratgher than each detail level?

Here is a rough example of what I'm trying to describe:

Area Table (*Overhead is only tracked at the Area level) 

Region Table

When I try and combine the tables in a visual, the total Overhead for each Area is repeated for each Region.

What I'd like to do is have the Overhead only show up at the Area summary level, something like below.

Any way to do something like that in a visual?

[Jason_Walker]

Wanted to come back to this thread with the solution I found for those with the same issue. Big thanks to @Daniel29195 for mentioning the ISINSCOPE function as that is what ultimately worked. Here is the measure that worked and left no data in the Region rows for overhead.

Overhead = if(ISINSCOPE('Df-dim-Region'[Region]),BLANK(), sum('Df-Overhead'[Overhead Amount])).

[Daniel29195 · Accepted solution]

@Jason_Walker 

what you want to do is possible using dax . function isinscope if you are using a matrix,

example of the synthax : 

measure = 

switch(

true() , 

isinscope ( region) ,  balnk() , 

isinscope ( area ) ,  [Overhead for each Area calculation ]

let me know if it works for you .

If my response has successfully addressed your issue kindly consider marking it as the accepted solution! This will help others find it quickly. I would appreciate hitting that kudos button 👍🤠

[Jason_Walker]

I'm not quite sure how your formula works. Here are my tables:

Df-Area Stats

Area

Overhead

Df-Region Stats

Region

Area

Sales

Expenses

Then I have two dimension tables

Df-dim-Area

Area

Df-dim-Region

Region

So would your formula look like this?

Overhead by Area =

switch(

    true(), 
    isinscope('Df-dim-Region'[Region]), blank(),

    isinscope('Df-Area Stats'[Area]), 'Df-Area Stats'[Overhead])

Or maybe add a [Region] column to my 'Df-Area Stats' table (with just blank values) and join that to my dimension table?