Show element even when it is filtered out

[SMR1989]

Hello everyone,

I've got a matrix in Power BI representing the sum of values for some elements. Within some of the elements there are calculations, for example: 

Actual representation of the Matrix

To achieve this representation, I've created a column with all the values represented in the matrix (named "Column1"). The values of the column are: "A", "B", "C", "SUM A+B+C", "D", "SUM A+B+C+D".

The measure created is like the following:

Value2 = 
VAR CalcABC = CALCULATE([Value], FILTER(ALL('Groups'), [Column1] in {"A","B", "C"} ))
VAR CalcABCD = CALCULATE([Value], FILTER(ALL('Groups'), [Column1] in {"A","B", "C", "D"} ))

RETURN
 SWITCH(
     TRUE(),
     CALCULATE(SEARCH("SUM A+B+C+D", SELECTEDVALUE('Groups'[Column1]),1,0), ALL(Calendar[Date])) > 0, CalcABCD,
     CALCULATE(SEARCH("SUM A+B+C", SELECTEDVALUE('Groups'[Column1]),1,0), ALL(Calendar[Date])) > 0, CalcABC,
     [Value])

So far the matrix behaviour is correct. However, when a filter by "Column1" is applied, I would need the matrix to show the following result:

Desired representation of the matrix when A and B are selected

When A and B are selected in "Column1", I need them both to be represented in the matrix but also the calculations should sum up only the two selected values.

The actual representation of the matrix is like the shown below:

Actual representation of the matrix when A and B are selected

Both calculated values disappear from the matrix because they are filtered out.

This is a fake example because I can't show you the real data, but in the real report there are hundred of elements. Users will be continously applying several filters and they can't select the calculations all the time.

I would appreciate any help!

[amitchandak]

@SMR1989 , what is your source data here .

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

[SMR1989]

Hi @amitchandak , thanks for your reply.

Here is a pbix file with the example:

https://www.dropbox.com/s/pdlaz6zwuun3bza/PowerBI_Example.pbix?dl=0

And below the Excel file:

https://www.dropbox.com/s/dv36t691t30l9cq/ExamplePBI.xlsx?dl=0

[v-yiruan-msft · Accepted solution]

Hi @SMR1989 ，

I just updated your sample pbix file, you can get it from this link. Later please check if that is what you want. Any comment or problem later, please feel free to let me know.

1. Create a table with unique value of Column1 in table Groups

2. Create a slicer with the field "Column1" in new table

3. Update the formula of measure "Value2" as below:

Value2 = 
var _selcol1=ALLSELECTED('Table'[Column1])
VAR CalcABC = CALCULATE(SUM(Data[Value]), FILTER(ALL('Groups'), Groups[Column1] in _selcol1 &&'Groups'[Column1]<>"D")) 
VAR CalcABCD = CALCULATE(SUM(Data[Value]), FILTER(ALL('Groups'), Groups[Column1] in _selcol1 ))
return 
 SWITCH(
     TRUE(),
     CALCULATE(SEARCH("SUM A+B+C+D", SELECTEDVALUE(Groups[Column1]),1,0), ALL(Calendar[Date])) > 0, CalcABCD,
     CALCULATE(SEARCH("SUM A+B+C", SELECTEDVALUE(Groups[Column1]),1,0), ALL(Calendar[Date])) > 0, CalcABC,
    CALCULATE(sum('Data'[Value]),FILTER('Groups','Groups'[Column1] in _selcol1 || SEARCH("SUM",'Groups'[Column1],1,0)>0)))

Best Regards

Rena