“Sigh.”

I pull my eyes away from the work I’ve been engrossed in to see the co-worker sat across from me, sat back in his chair, looking in defeat at the screen.

“What’re you working on?” I ask, curious to know the reason for the…

“Nothing much, man. Just this spreadsheet. I can’t get it to work,” the co-worker replies.

“Would you like to talk it through?” I ask, being quite partial to Rubber-Duck Debugging myself. Talking a problem through can lead to profound breakthroughs…

The problem is explained. I advise the co-worker to try a GROUPBY function—perhaps that will yield the result they want.

A day or two passes…

“Hey Joseph, would this work in Power BI instead?”

“Is this the same problem from the other day?”

“Yeah, was thinking it might be easier in Power BI.”

“We can give it a go. What exactly were you trying to accomplish again…?”

The co-worker had product sales data spanning multiple budget years, including date columns, and wanted to plot each item group’s net margin divided by the total number of budget years that item appears in, on a graph showing the months across the X-axis.

The data was stored in an Excel workbook after being dumped from the ERP system (Dynamics AX). This is an easy data storage medium for Power BI to ingest. Once the data had been loaded, I asked the co-worker:

“So which exact columns do you actually care about?”

The dataset provided had around 20 columns. After talking it through with the co-worker, only four columns were actually required:

• Budget Model
• Delivery Month
• Item Group
• Net Margin

Since the goal was to divide each SUM of the Net Margin for each Item Group by the number of times it appears in a Budget Model, the next step was to consolidate the data.

Grouping the data in Power Query, using the advanced tab, allowed for the first three columns to be our grouping conditions and the last column to be a SUM of Net Margin. This left us with a much more usable dataset.

The next challenge was figuring out how many times each DISTINCT Item Group appears in a Budget Model. After some Google-fu, the SUMMARIZE function in DAX was found:

1
2
3
4
5
6

SUMMARIZE

Returns a summary table for the requested totals over a set of groups.

Syntax:
SUMMARIZE (<table>, <groupBy_columnName>[, <groupBy_columnName>]…[, <name>, <expression>]…)

Ideal! I thought. All we need to do is create a calculated table using the SUMMARIZE function. We’ll use the grouped table, grouping by the Item Group, create a new column named “Count of Budget Model”, and populate this column with the result of a DISTINCT function, again using the Item Group column:

1
2
3
4
5

DISTINCT
Returns a one-column table that contains the distinct values from the specified column. In other words, duplicate values are removed and only unique values are returned.

Syntax:
DISTINCT(<column>)

Awesome! Now, over to the Relationships tab, where we create a Many-to-One relationship between the Item Group column from Table1 and the Item Group column of our newly created table.

With this relationship in place, we can add a new calculated column to Table1 that looks at each row in the Net Margin column and divides this amount by the related Count of Budget Model value, using the RELATED function:

1
2
3
4
5

RELATED
Returns a related value from another table.

Syntax:
RELATED(<column>)

Finally, plot a Column Chart with dates on the X-axis and the newly calculated column on the Y-axis, throw in an Item Group slicer on the page…

🎉 Co-worker happy, I’m happy. 🎉

Job done.