Conditional filtered average in line chart

[Anonymous]

Hi,

The situation is as follows: I've got a line chart, with the date on the x-axis and the n of sales on the y-axis. The legend is set to Data[Country]. Country is part of a hierarchy (Country > Region > City). I've got a separate filter visual for this hierarchy. 

What happens right now is that when I filter on a layer lower than Country, for example a specific city, the line chart shows one line with the data for only this city. What I want however, is that when I filter on a layer lower than Country, the visual shows one line with the total sales for the corresponding country (E.g. when filtering on New York, total sum is shown for the US). 

I've tried this with a measure in the Values field of the visual, but I can't get it to work.

Hope y'all can help me out! 

Kind regards.

Measure = 
IF (
    ISFILTERED ( Data[Region] || Data[City] );
    CALCULATE ( SUM ( Data[Sales] ); ALL ( Data[Country] ) );
    SUM ( Data[Sales] )
)

[AlB · Accepted solution]

Hi @Anonymous 

If I understand correctly, you always want the value for the country regardless of the level selected at the slicer. If so:

Measure =
VAR country_ = SELECTEDVALUE ( Data[Country] )
RETURN
    CALCULATE (
        SUM ( Data[Sales] ),
        Data[Country] = country_,
        ALL ( Data[Region], Data[City] )
    )

Or since you have Country in the legend, this should suffice:

Measure =
    CALCULATE (
        SUM ( Data[Sales] ),
        ALL ( Data[Region], Data[City] )
    )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

This works like a charm, thanks so much!

Have a good one 🙂