Forum Discussion
Skilled SharerHow to do a sumifs formula
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
2 Replies
GilesWalkerI 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_SongImpactful Individual
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 0Then 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.