cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
luvagoldenk9
Helper I
Helper I

Bar Chart with Line from Another Table

Hello,

 

I am implementing a bar chart with a line. The bar chart is based on values from Table A and sums number of calls by month:

luvagoldenk9_0-1648477273202.png

There is a separate Table B that contains # of surveys returned (here is a sample):

FY21 Oct     5

FY21 Nov    1

FY21 Dec     2

 

The 2 tables are connected via the same year/month (for example ... FY21 Oct). The Table B values are giving sum as a flat line:

luvagoldenk9_1-1648477622003.png

 

The visual line needs to show specific months (here is an example of another visual):

luvagoldenk9_2-1648477690115.png

 

Is there a way for yellow line not to be a flat sum line? Can a measure of some type be used?

 

Thank you in advance for your review!

 

3 REPLIES 3
v-polly-msft
Community Support
Community Support

Hi @luvagoldenk9 ,

Please refer to my pbix file to see if it helps you.

Create a measure.

Measure =
CALCULATE (
    SUM ( 'Table (2)'[mnvalue] ),
    FILTER (
        ALL ( 'Table (2)' ),
        'Table (2)'[date] = SELECTEDVALUE ( 'Table'[date] )
    )
)

 

vpollymsft_1-1648711716593.png

 

 

If I have misunderstood your meaning, please provide your pbix file without privacy infromation and desired output.

 

Best Regards

Community Support Team _ Polly

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

amitchandak
Super User
Super User

@luvagoldenk9 , You should use a common FY /Date table, There you should have these FY Month columns. Join with both tables, Use the common table on axis

 

Try to be in star schema

https://docs.microsoft.com/en-us/power-bi/guidance/star-schema

 



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Hello amitchandak!

 

Thank you for your quick response.

 

Unfortunately that did not seem to work as I received the same results. Perhaps I missed something:

  • I created a Date Table (Jan 1 2020 thru Jan 1 2030) and calculated the corresponding "FYyy mmm" values
  • created a relationship between the Date Table and Table A and Table B - key field was "FYyy mmm"
  • used the Date Table "FYyy mmm" in the bar/line chart
  • result is the same flat line instead of showing value for each individual month (plus now it lost the sort order of the months)

Table A is a details table and contains details about each call (call date, name, issue, etc.)

Table B is a summary table and only has totals by each month so:

FY21 Oct     5

FY21 Nov    1

FY21 Dec     2

 

The visual seems to be summing all of the Table B values and the line is representing the total number of surveys instead of each individual month.

 

Thoughts?

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors