MrExcel.com - Your One Stop for Excel Tips & Solutions

Prevent Power BI from Adding Up Year Fields

April 28, 2023 - by

Prevent Power BI from Adding Up Year Fields

If a column contains all numeric values, Power BI Desktop will assume that you want to sum that column. This makes a lot of sense for fields like Revenue, COGS, and Profit.

The Sigma symbol appears next to COGS, Profit, Quantity, Revenue.
Figure 1099. Defaulting to sum for Revenue is perfect.

However, Power BI Desktop sees the Year and Month Number fields as 100% numeric and wants to sum those fields. That does not make sense.

Unfortunately, Power BI thinks they should sum MonthNo and Year.
Figure 1100. The Sigma next to MonthNo and Year will cause problems.

Click on the word MonthNo in the above. In the Modeling tab, open the Default Summarization drop-down and choose Do Not Summarize. Repeat for Year.

Change the Default Summarization to Do Not Summarize.
Figure 1101. You don't want to Sum things like Account Number, SKU, or Year.

By default, month names will sort into alphabetic sequence. Click on the Month field in the Fields list. On the Modeling tab, open the Sort by Column and specify that Month should be sorted by Month Number.

For Month Name, choose Sort by Column and choose Month Number.
Figure 1102. Make sure that month names are sorted by month number.

This article is an excerpt from Power Excel With MrExcel

Title photo by Will Porada on Unsplash