Line chart troubleshooting when using multipile tables with dates.

[Anonymous]

I have a line chart that is combining two tables. One table is a list of units to be shipped by day (but not every day is a day that ships) and days have multiple shipments (or rows). The other line chart is a daily goal which is a total by day. I want to display both of these lines on one chart. I can get the units shipped by day to display when I set the line chart to count or sum. But when I add the goal line (which comes from another table) the chart sums all the days together.

I think my issue is that my shipment table has multiple rows per day (one row for each shipment) vs. my goal table has only one row for each day so for the join it duplicates the goal for every day that has multiple shipments (or rows)

Below is how the chart looks currently, the red line is what I'm trying to add (I drew it by hand)

Below is what the chart looks like when I set it to average...but this is the average across all days...I want an amount that varies each

day

If I change the goal line to be a sum it seems to sum all the days that have a goal

Here is the issue I think my shipment table has multipile rows so days are repeated.

My goal table (which is joined to above table by date only has one goal for each day.

Here is how the tables are joined which is thru a Date table

[v-eachen-msft]

Hi @Anonymous ,

Try the following measure:

Measure = 
CALCULATE(SUM('Table'[Capacity]),ALLEXCEPT('Date','Date'[Date]))

[Anonymous]

Thanks I tried that and it appears to still be summing all of the days. See below for screen shot

[v-eachen-msft]

Hi @Anonymous ,

Is the date on your x-axis the same as the date you used in the measure?

If not, you could try the same date ( from the same table ).

[Anonymous]

Thanks for the idea.  When I type the formula in PowerBI preopopulates with a number of date choices (but none of the choices are the same date that I'm currently using on the X axis). Maybe this is the issue? I tried a number of the default choices but none of them work

[Greg_Deckler]

Perhaps MAX? If the goal is repeated every day, just take the MAX of it, or the MIN.

[Anonymous]

Yes, I tried both Max and Min and I get the same result as if I take the average (right now the goal is the same for every day...but it will change once I figure out how to get this chart to work)