cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

Weekly Forecast and capcity Chart

I have a table showing multiple disciplines names, week date and number of employees in discipline for that week. I want to plot this in secondary Y Axis in column line combo chart but it’s summing the data for all weeks and shows a straight line (that summed number for all weeks in the plot). Any suggestions plz

1 ACCEPTED SOLUTION
Super User

@arp2 Updated your PBIX, attached below signature. Your Week to Week relationship is inactive so you need to use USERELATIONSHIP in a measure.

Projected Capacity Measure = CALCULATE(SUM('Table2'[Projected Capacity]), USERELATIONSHIP(Table1[Week], 'Table2'[Week]))

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
6 REPLIES 6
Super User

@arp2 Generally this means that there is a missing relationshp in the model.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Helper II

DummyDataTable1 2Feb.xlsx

The dummy PBI file is available at following link.

https://1drv.ms/u/s!Ag_LqrJ6gGmfllbA6ZrdJLdiGkQL?e=WEvQwb

What's happening is, when I add Capacity/Projected Capacity to secondary Y axis, it is summing the value for selected discipline for all weeks in table and showing flat line (summed number) whereas I want weekly number in the chart

Super User

@arp2 Right and I'm saying that is generally because there is a missing relationship between 2 tables and thus all of the rows in that second table are in context versus being filtered to the correct rows via the relationship.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Helper II

@Greg_Deckler Got that! I tried but I am unable to solve it. Can you plz have a look at PBI dummy? https://1drv.ms/u/s!Ag_LqrJ6gGmfllbA6ZrdJLdiGkQL?e=WEvQwb

Super User

@arp2 Updated your PBIX, attached below signature. Your Week to Week relationship is inactive so you need to use USERELATIONSHIP in a measure.

Projected Capacity Measure = CALCULATE(SUM('Table2'[Projected Capacity]), USERELATIONSHIP(Table1[Week], 'Table2'[Week]))

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Helper II

@Greg_Deckler This solved the issue and thank you very much for the help.

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors