Show average and selected user data in a line chart

[MartaCB]

Hi,

I have a set of data for a number of users (user1, user2, etc). I want to be able to show the data in a line chart showing at the same time:

- Average

- Any selected user data.

This is only for a line chart, not for a whole page.

Thank you! 🙂

[MartaCB]

Hi Felix,

I think we are getting there but not yet! (Sorry!). Thank you for being there this way 🙂

Following your example, the average I get it's not the overall average (by month), it's just it's a constant line, just the average of the selected flat (sum of all months energy consumption divided by 12 months) .

What I need is to see flat x and the total average by month for the whole data set. So I can compare the energy consumption of my selected flat for January against the average January, February against average February, etc.

I hope that makes sense.

[MFelix]

Hi @MartaCB ,

The measure that you add is that average when you select a specific flat. For the values I have the averages are:

Flat 1 - 1: 10 2: 15 3: 12 = 12.33

Flat 2 - 1: 10 2: 16 3: 10 = 12

Flat 3 - 1: 12 2:13 3: 10 = 11.67

In the chart has you can see the line reflect those averages per flat

[MartaCB]

Hi @MFelix 

Yes, that is what I do NOT need. What I need is to compare "apples with apples". So if I am looking at January, I need to be able to see the total average for January. The constant line that shows the year average by flat does not answer this question. 

Using your table as an example:

Flat	Month1	Month2	Month3
Flat 1	10	15	12
Flat 2	10	16	10
Flat 3	12	13	10
Average	10.7	14.67	10.7

Following your example, I can plot flat 1, 2 and 3 but not the average. That is what I need to plot even when I am only seeing flat 1.

Thank you

[MFelix]

Got your point then you need to redo this approach.

First change the metric to:

Average Measure = CALCULATE(AVERAGE('Table'[Comsuption]), ALL('Table'[flat]))

Create a table with flat values and an additional line that says average the following code will work:

Flat +  Average = UNION(DISTINCT('Table'[flat]), ROW("flat", "Average"))

Add the following metric to your model:

Value for chart = SWITCH(
                        SELECTEDVALUE('Flat +  Average'[flat]),
                        "Average", [Average Measure], 
                        CALCULATE(SUM('Table'[Comsuption]), TREATAS(VALUES('Flat +  Average'[flat]), 'Table'[flat])
                        ))

Format you line chart has following:

• X-Axis - Month
• Y-Axis - Values for chart
• Legend - Flat (new table)

[MartaCB]

Hi @MFelix ,

Apologies, I could not test it until today. It does not work for me. I think I understand what you do but the average line is not plotted. Is there any possibility that you could share your model so I can check what I may be doing wrong? Sorry but, it is just it does not work for me.

[MFelix]

Please see the file attach.

[MartaCB]

Hi @MFelix ,

Apologies for the delayed reply. I had to pause the Power BI development. I am now back at it.

I downloaded your file and I really don't understand what is the difference as the functions are the same.

Your file shows the average as a static value, if does not matter what filter or slicer I apply, the average is always the same.

For my report, that is not the case, I can add the average measure but in the moment I click on the slicer, I get the average for flat 1 or whichever I click on.

If I only click on the "average" row of the new table, it goes blank.

I don't really know what to try since it works for you but not for me.

Thank you

[MFelix]

Hi @MartaCB ,

For this you need to create a measure with the average something similar to:

Average values = CALCULATE(AVERAGE(Table[Column]), ALLSELECTED(Table[User]))

Be aware that without data is difficult to give you the correct expression.

[MartaCB]

Hi,

Thank you for a quick answer. I had created the average measure and I am able to represent it. The  challenge is representing this "average" at the same time I show the dataset for a single user (row of data).

In my mind the outcome I need is a line chart that has the average and a drop down that adds second line for an user-x.

Hope this makes sense and sorry if it did not before

[MFelix]

You need to add the average measure and the values to your chart that way you will achieve needed information.

[MartaCB]

Hi,

Please, see below the example. Every "user" is a flat and I want to see the energy consumption throughout the year.

I am able to add the measure average, but unable to add a single flat to visually compare it against the average:

The column energy consumption contains all the consumption by year and by flat, similar to:

flat	month	energy consumption
flat 1	jan feb march  etc	number01 number02 number03 etc
flat 2	jan feb march  etc	number10 number11 number12 etc
etc	jan feb march  etc	number21 number32 number33 etc

With this data, I do not know what transformations, modifications, tables and/or measures I have to add to get a line chart that adds  the average measure (currently achieved) and a chosen flat  (how do I add a drop-down to chose a flat? how do I add this chosen flat?)

I hope the case now makes more sense. 

Thank you,

Marta

[MFelix]

Hi @MartaCB ,

For this you just need to add the following measure to your model:

Average Measure = CALCULATE(AVERAGE('Table'[Comsuption]), ALLSELECTED('Table'[Month]))

Now add this as a secondary line on your chart:

[MartaCB]

Hi Felix,

Thank you for trying again but that is not what I need. I tried my best  using paint below

The line chart has to list the average (currently there in purple) and then, a chosen flat in another colour.  I represented flat 1 and flat 2 only so show "variation" but I need a drop down to be able to say: "apart from the average (always shown), show me this flat"

THank you

[MFelix]

Now I get what you need.

Format you line chart has following:

• X-Axis - Month
• Y-Axis - Values per flat
• Legend - Flat
• Advance options
  • Reference line: Y-Axis Constante line
    • Average Measure

Check pbix file attach