Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
luvagoldenk9
Helper II
Helper II

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-rongtiep-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

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.