Forum Discussion
How to do a sumifs formula
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.
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.