Function Problems

Calculate a Total for Multiple MSAs with an Excel DAX Measure

Maira MehtabMaira Mehtab Nov 14, 2026 868 views

Question details

The user needs a DAX measure that totals Grossed Room Nights for more than one MSA.

Calculate a Total for Multiple MSAs with an Excel DAX Measure
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the measure dialog

In Excel, select Power Pivot > Measures > New Measure and choose the All_Years table.

2
Enter the measure

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").

3
Add it to a PivotTable

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.

Use an OR condition in a Power Pivot DAX measure
Existing MSA filters: This CALCULATE filter replaces an existing filter on All_Years[MSA]. Filters on other columns continue to affect the total.
Free Microsoft Office alternative

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.

Calculate totals for selected MSAs with worksheet formulas such as SUMIFOpen and edit Microsoft Excel XLSX and XLS filesWork in a familiar spreadsheet interfaceUse a free, lightweight office suite
QA img-9

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).