Bar Graph with 3 topics showing average and last result

[Gowmadrid82]

Hi, I am hoping someone can help me with an issue. I want to create a visual that shows the average of 3 columns in a bar chart, then overlay that with the last result received for each column. I have created a dummy mockup and the three columns are CoolScore, FitScore and IntelligentScore. I firstly created a measure to get teh average of each column

e.g. Average Cool Score = AVERAGE('Scoring Results'[CoolScore])

I then created an axis table so that I could add the measure to the graph

I then created a Switch to link the ID's to the average score measures

That all works well so far and I get the 3 columns added as averages to the line and clustered bar chart 

Now I want to overlay the last result received for each column and position that on each bar. 

So next I thought I need to create a measure to get the last result of each column, so I created the measure below for each column

This gives me the desired results

If I add the measures to the Line Y axis, it kind of gives me something to indicate the last score, but as a line across all bar charts. Not what I want (see below)

 So then I thought, well do I just create another axis table and switch to link the ID to values for the last results - so I did just that

That basically just adds up the three last value results and creates a line across the bar charts

So then I thought, well just add the average measures directly to the column y-axis and then last value measures to the line y-axis, that then gives me more of what I want, but the last value results to overlay on each bar chart, instead they are just in the middle

Some help would be really appreciated. Am I missing something obvious or what is the best way to tackle this issue?

[NickolajJessen · Accepted solution]

Aight, 
First of, i'm not sure what constituted the "Latest" entry, so i guessed it was the highest id being the latest entry?
We want to be able to locate the latest entry, which we can do with something like this:

The highest ID within each attrbute will return 1.

Second you go and create yourself a measure, where you now reference this latest row as so:

Latest score = 
CALCULATE(
    SUM('ScoreResults Graph'[Value]),
    'ScoreResults Graph'[rank] = 1
)

Easy, ye?

Third you go ahead and add it to a coumn and line grand and add it as the line

now, you might be wondering where the line is?
It's there! It just have 0 width!
It can be adjusted in Shapes and pen width (I think the translateion is):

Lastly, so make the "Line" values pop  to the bottom, make the secondary Y-axis start from 0 and end at 400 ish. Do it manually for dynamically as so:

you might also need to change the data labels placement from "Automatic" to "Below" or "Above" the line.

[Gowmadrid82]

Ignore me, to get to the image you showed me I can see that I just need to set the line width to zero rather than have it set at a value greater than zero

Thank you so much, this has been very helpful

[NickolajJessen]

No problem :)! 

Make sure to accept it as a solution if it solves you problem
Appreciate your kudos

[Gowmadrid82]

That is great, thank you. Yes, the highest ID being the last result. 

So I have nearly created what you have provided

How do I get them to show as bars in the graph and not a line across it. What have you done differently for that? It is hard to follow your exact steps as it is in a different language. So I have the following

[Gowmadrid82]

I have uploaded the pbix file https://we.tl/t-fesbfKKAl9 

I have experimented quite a bit, although the main table to look at is the Scoring Results. The columns with the scores that I am trying to average and then show the last result are the CoolScore, FitScore and Intelligent Score. 

There are some axis tables that I used to add all three scores to the one graph (as illustrated above). I also created a new table called ScoreResults Graph that is a copy of the Scoring Results but only has the columns required. I then unpivoted all columns but the ID column to also give all results in one graph, but again not sure how I just put the last value over the top of those. 

Thanks in advance for anyone helping with this, it has been driving me crazy trying to think of a solution and fear that I may be overcomplicating it the more I look into it 🙂

[Gowmadrid82]

Of course. Apologies if I am missing the obvious, this is my first time posting and not sure how you attach the .pbix file.  I can't see a file attachment option. 

[NickolajJessen]

Through dropbox, google drive, wetransfer or your preferred sharing software

[NickolajJessen]

For something like this where a lot of experimentation might be required, it would be a good idea to share the .pbix or dummy .pbix for quick and accurate support

[Gowmadrid82]

Do I take it everyone is stumped by this and does not know an answer? If that is the case, that makes me feel slightly better that I wasn't able to work this one out. Although, not sure where to go next with this. 

[NickolajJessen]

My apoligies, i was hung up at work yesterday. Give me 10 minutes and i'll have something for ya.

[NickolajJessen]

@Gowmadrid82 

just before i start explaining, is this the kind of result you are looking for?

[Gowmadrid82]

Yes, that is perfect. Other than the average not requiring the decimals, that is absolutely perfect. Thank you so so much, that is brilliant. 

So please do enlighten me, how did you achieve this? 🙂