How to do a sumifs formula

[GilesWalker]

Hi everyone,

I have been trying to figure out the best way to show this data. I have this table in excel:

Route	Mon	Tue	Wed	Thu	Fri	Sat	Sun
BAY-BIE	0	12	12	12	12	12	0
BIE-BAY	10	10	10	10	0	10	0
BIE-BTN	66	66	66	66	66	66	0
BIE-LTN	28	28	28	28	28	28	0
BTN-BIE	58	58	58	58	58	58	0
BTN-DPO	12	12	12	12	12	12	0
DPO-BTN	12	12	12	12	12	12	0
ETJ-BAY	10	10	10	10	10	0	0
ETJ-BIE	12	12	12	12	12	12	0
ETJ-BTN	16	16	16	16	16	0	0

The Routes relate to the same routes in the company database. The numbers in the table are forecast numbers for each day. I need to compare these against the actual numbers on that route on any given day. For example, the forecast for route BIE-BTN is 66 each day except Sunday.

In PBI I have a standard date key table created using calculated columns, one of those columns contains the day of the week (Mon, Tue, etc) I have also entered columns with the above figures as calculated columns, eg: 

BIE-BTN = IF(DateKey[Day]="Sun", 0, 66)

I need a graph to be able to show all the routes on the X-axis then on the first Y-axis the actual delivered numbers, then on the second Y-axis the forcasted number. The forecasted number needs to be a dot point over the relevant route. The problem I am running into is that I cant get PBI to do this. The picture below shows the actuals delivered in the bars, and the forecast for BIE-BTN

The line goes across all routes in the graph.

I have tried showing this data in a table format and the same issue arises. Open to suggestions on if there is a better way. At the moment in appears as though it will be a seperate graph for each route which is not ideal.

Thanks,

Giles

[GilesWalker]

I came up with a possible fix. I have changed the table (called Plus 50 forecast) with the forecast numbers in to be:

Route	Totals
BAY-BIE	12
BIE-BAY	10
BIE-BTN	66
BIE-LTN	28
BTN-BIE	58
BTN-DPO	12
DPO-BTN	12
ETJ-BAY	10
ETJ-BIE	12
ETJ-BTN	16

These numbers dont change except for certain days where they are zero. Then I created two measures:

Count of days = COUNTA(DateKey[Date])

This counts how many days are selected in the date slicer on the page.

This is then joined to this measure:

Forecast = SUM('Plus 50 forecast'[Totals])*[Count of days]

This measure is placed in the line section of the Line and Bar chart. Now I get the correct forecast number above the relevant route on the x-axis. However how can I get the measure to account for the days which are 0 forecast? Currently it just takes the number from the above table and multiplies it by however many days are selected.

Hope someone can help with this.

[Vicky_Song]

How about you change the data with the following format:

Route Date Values
BAY-BIE Mon 0
BAY-BIE Tue 12
BAY-BIE Wed 12
BAY-BIE Thu 12
BAY-BIE Fri 12
BAY-BIE Sat 12
BAY-BIE Sun 0
BIE-BAY Mon 10
BIE-BAY Tue 10
BIE-BAY Wed 10
BIE-BAY Thu 10
BIE-BAY Fri 0
BIE-BAY Sat 10
BIE-BAY Sun 0 

Then add the a cacluated column with the formula: Forecast = CALCULATE(SUM([Values]), ALLEXCEPT('Plus 50 forecast', 'Plus 50 forecast'[Route]))

Or, you need to calculate the total value of all columns in each row in the Excel file first.