Forum Discussion

MartaCB's avatar
MartaCB
Frequent Visitor
3 years ago

Show average and selected user data in a line chart

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 ...
Need Help
MFelix's avatar
MFelix
Icon for Super User rankSuper User
3 years ago

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's avatar
MartaCB
Frequent Visitor
3 years ago

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.

    • MartaCB's avatar
      MartaCB
      Frequent Visitor
      3 years ago

      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