Line Chart Visual

[AaronGlenn10]

Power BI Rock Stars:

I have set of sales data broken down by cities in Texas - I would like to compare the sales trends in one city [e.g. Dallas] to the trends of the entire state.  How would I filter and/or configure in order to represent this comparison on a line graph?

Thank you, ~AGS

[TomMartens]

Hey @AaronGlenn10 ,

I create two measures one that creates the Average of all SalesTerritoryRegions (your cities):

__Average All SalesTerritoryRegion = 
CALCULATE(
    AVERAGE(    'FactOnlineSales'[SalesQuantity] )
    , ALL( 'DimSalesTerritory'[SalesTerritoryRegion] )
)

the second calculates the same but now for the selected SalesTerritory

__Average Selected SalesTerritoryRegion = 
CALCULATE(
    AVERAGE(    'FactOnlineSales'[SalesQuantity] )
    , VALUES( 'DimSalesTerritory'[SalesTerritoryRegion] )
)

Then I use both measures on the line graph, see the picture below:

Hopefully, this provides an idea how to tackle your challenge.

Regards,

Tom

[AaronGlenn10]

Tom, thank you for the response.  Is it possible to reference a measure within a measure.  I'm trying to compare the YoY percentages of the city versus the state, and can only reference data/values in the fact table.  I have a YoY percentage measure, just can't reference it in the measures you provided above.

[TomMartens · Accepted solution]

Hey @AaronGlenn10 ,

basically it's possible to reference a measure inside another measure, this is something I do quite often: I write a base measure, then I use this measure to change the filter context.

This is how it looks

Measure 2 = 
CALCULATE(
    [base measure]
    , [filter expression]
)

For this reason I'm not sure why you can't reference your YoY percenage measure.

If you need more guidance, please consider to create a pbix that contains sample data but still reflects your data model. Upload the pbix to onedrive or dropbox and share the link.

Regards,

Tom

[MattAllington]

This is how I would do it. 
1 write a measure for the state and place it into the values

2 write a measure to select a single city and place it into values

3 add a slicer on the city column

4. Select the city from the slicer to compare with the state. 

meaure 1 something like CALCULATE(SUM(dataTABLE[value]),All(dimtable[city]))

 you should create a dimension table for the city (that is what I would do) and that will affect the formula as shown. 

the city measure needs a disconnected dim table for the cities (that's 2 identical tables, only one is joined). 

CALCULATE(SUM(datatable[value]),dimtable[city]=SELECTEDVALUE[disconnectedtable[city])

keep in mind these simple measures above will give you simple sums, and the state will be orders of magnitude larger than each city. Just replace the sum with some other proportional calculation, such as % change vs Last Year

[AaronGlenn10]

Matt, thank you.  I'm a bit of a database novice ... can you please help me explain how the dim table works and how to create.  I think you're saying I need two new dim tables?

[MattAllington]

Everyone is a beginner at some point it time.  If you want to learn, you will have to do some reading/learning.   I cover the concept in this article here https://exceleratorbi.com.au/the-optimal-shape-for-power-pivot-data/

You need 2 dim tables, one joined (1:many) to your data table and the other not joined at all.