How to ignore filters to populate a Matrix

[TimK]

Hi,

Hope someone can help!

I have a data table with 2 levels of data..... and it is linked to 2 [tag] files, each with unique lists of data tags.  I have 3 titles in the upper tag list, and 5 in the sub list.

So I have populated the matrix with rows containing the 2 sets of linked tags.

Now the crux!

In terms of data, I have figures for each of the 3 titles at the higher level, but not for all the titles in the lower level.

so, if I just drop those figures into the matrix, some of the lower level titles have no figures against them.

What I am trying to achieve is to have a value for each of these lower titles - and where there are not values, to replace them with the sum of all the figures at the higher level!

Is this possible?

Thanks for any help

[TimK]

sure, i will try and explain here using tables:

so i have a measure that creates a table like this, based on data from several sources - hence the measure.  Please remember, the value shown below are calculated using a measure, and are not in the original base data file if that makes sense:

Project	DelD	Value
A	AA	2
A	BB	3
A	DD	4
B	AA	2
B	CC	3

Simplified to try and explain what the issue is.

I then have 2 [tag] files

Project

A

B

DelD

AA

BB

CC

DD

These 2 files have relationships to the data file at the top.

So what i am trying to achieve is an output like this:

Project	DelD	Value
A	AA	2
A	BB	3
A	CC	9
A	DD	4
B	AA	2
B	BB	5
B	CC	3
B	DD	5

You will see that there are 3 lines where there is no data at the lowest level, and what i need is for a measure to then give me the sum of all the values at the higher level - so for A it is 2+3+4=9 and for B it is 2+3

Does that make sense?

The key is that the data values are created using complicated measures that are dynamic

Thanks for any help

[v-easonf-msft · Accepted solution]

Hi, @TimK 

Pleasae  add a new measure like:

Result = 
VAR val =
    SUMX ( ALL ( tag2[DelD] ), [Value] )
RETURN
    IF ( ISBLANK ( [Value] ), val, [Value] )

If it doesn't work ,please share more details.

Best Regards,
Community Support Team _ Eason

[TimK]

hi @v-easonf-msft 

I am now strugling to get the Measure column to now add up correctly - so in your answer we have the Result column - how can i get it to add up to 33 rather than the 14?

Thanks for any help

[v-easonf-msft]

Hi, @TimK 

You can add a new measure like:

Measure = 
var tab=SUMMARIZECOLUMNS(tag1[Project],tag2[DelD],"_Result",[Result])
return IF(ISFILTERED(tag1[Project]),[Result],SUMX(tab,[_Result]))

Best Regards,
Community Support Team _ Eason

[TimK]

hi, @v-easonf-msft 

I am so sorry to keep coming back to you for your valuable help!!!!

I have updated the file and it is attached here How to ignore filters to populate a Matrix-2.pbix 

You will see i have updated it a little, and added another set of tags..... [Page2] is the new data and you will see i have 3 levels of categories [Project], [L/M] and [DelD]

The concept is the same, in that there are a few [Project] where there is no [DelD] and you will see that i have now set it to the average of the other [DelD] and that bit works [Results2]

What is not working are the sub totals at the [L/M] and [Project] levels

For example, at the [A], [L] level it shows 21 whereas it should be 42, and for example at the [A] level it shows 31, whereas it should be 62 - so as far as i can see, it is not including the average values.

Is there anything you can do to help me???

Many thanks

Tim

[TimK]

thanks for your help

[v-easonf-msft]

Hi, @TimK 

Can you share relevant screenshots or sample files to further explain your needs?

Best Regards,
Community Support Team _ Eason