How to stop a measure disappearing from a graph

[David4]

I have a report with two visuals:

a table like this:

Store Type	Spend Per Head
A	£10.00
B	£11.00
C	£12.00
Rest of Estate	£15.00

and a graph with weeks on the x-axis and two measures as below:

Measure 1 calculates the Spend Per Head for the "rest of estate" stores:

Spend Per Head RoE =CALCULATE( [Spend Per Head] , FILTER( ALLSELECTED(Stores) , Stores[Store Type] = "Rest of Estate" ) )

and Measure 2 calculates the Spend Per Head for anything other than the "Rest of Estate" i.e. store type A. B and C:

Spend Per Head exc RoE = CALCULATE( [Spend Per Head] , FILTER( Stores , Stores[Store Type] <> "Rest Of Estate" ))

When I click on, say, "A" on the table, the graph changes so that it only shows the line for "Spend Per Head exc RoE". Is there any way of adjusting the "Spend Per Head RoE" measure to stop it from disappearing when another store type is selected from the table visual?

[Sean · Accepted solution]

Hi @David4,

I believe you'll need a Store Type dim table to achieve this.  (or @OwenAuger ? )

Look at this article...

https://www.sqlbi.com/articles/understanding-dax-auto-exist/

"The golden rule of data modeling is always the same: always use star schemas. If a column has to be used to slice and dice, then it needs to belong to a dimension. Numbers to aggregate, on the other hand, are stored in fact tables. Tabular lets a developer deviate from the regular star schema architecture. This does not mean that doing it is always a good idea. It seldom is."

Then your Measures would look like this...

Spend Per Head exc RoE =
IF (
    SELECTEDVALUE ( 'Store Type Table'[Store Type] ) = "Rest of Estate",
    CALCULATE (                        // If someone clicks Rest of Estate in the table
        [Spend Per Head Measure],      // you'll get the Total instead of 0
        FILTER ( 
            ALL ( 'Store Type Table' ),
            'Store Type Table'[Store Type] <> "Rest Of Estate"
        )
    ),
    CALCULATE (                        // If a Store Type is selected
        [Spend Per Head Measure],      // then you'll get the total only for that store
        FILTER (
            'Store Type Table',
            'Store Type Table'[Store Type] <> "Rest Of Estate"
        )
    )
)

Spend Per Head RoE = 
CALCULATE (
    [Spend Per Head Measure],
    FILTER (
        ALL ( 'Store Type Table' ),
        'Store Type Table'[Store Type] = "Rest of Estate"
    )
)

This is the result and as you can see when Store Type A is selected in the Matrix on the left the chart is not affected!

Hope this helps!

[David4]

Thanks Sean, this looks like it's giving me exactly what I need!