Calculate a Total for Multiple MSAs with an Excel DAX Measure
Question details
The user needs a DAX measure that totals Grossed Room Nights for more than one MSA.

- Product
- Microsoft Excel Power Pivot and Data Model
- Device & OS
- not provided
- Scenario
- Calculate a combined total for MSAs such as Santa Rosa, CA and Chico, CA in an Excel Data Model.
- Observed behavior
- The desired measure includes rows matching either MSA in one total.
Check that the All_Years table is in the Excel Data Model and that its MSA and Grossed RN columns have the expected names. Confirm that the MSA text values match the source data exactly.
Use an OR condition in a Power Pivot DAX measure
Use this measure to combine two MSA values in a single Grossed Room Nights total.
CALCULATE evaluates the sum using a filter that accepts either MSA. Applying separate equality filters for both values to the same column would not produce the intended combined selection.
In Excel, select Power Pivot > Measures > New Measure and choose the All_Years table.
Give the measure a name, such as Grossed RN for Selected MSAs, and enter this formula in the formula box: =CALCULATE(SUM(All_Years[Grossed RN]), All_Years[MSA]="Santa Rosa, CA" || All_Years[MSA]="Chico, CA").
Save the measure, then add it to the Values area of a PivotTable based on the Data Model. Check the result against the source rows for both MSAs.

Calculate MSA Totals in WPS Spreadsheets
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and compatibility with Excel files for easy migration. For worksheet-based data, you can use SUMIF formulas to total selected MSAs; WPS does not fix Excel Power Pivot or its DAX measures.

Frequently Asked Questions
How do I include another MSA in the measure?
In Power Pivot > Measures, edit the measure and add another condition joined by ||, such as || All_Years[MSA]="Redding, CA".
Why not use separate equality filters for the two MSAs?
Separate equality conditions for different values of the same column can conflict. Joining them with || tells CALCULATE to include rows matching either value.
Will an MSA slicer restrict this measure?
The formula's filter replaces an existing filter on All_Years[MSA]. If the slicer filters a different, related table, its effect depends on the model relationships and filter context.
Can I calculate the total without a DAX measure?
For data in worksheet cells, use SUMIF for each MSA and add the results. For example, if column A contains MSAs and column B contains Grossed Room Nights, use =SUMIF(A:A,"Santa Rosa, CA",B:B)+SUMIF(A:A,"Chico, CA",B:B).




