Create Summarize table

Problem

In this scenario, the requirement is to create a table based on the following fields:

• Part Number
• Lemon Score
• Lemon Score Category
• Month-Year

Here, both Lemon Score and Lemon Score Category are measures, not columns.

---

Solution

Since measures return scalar values and cannot be directly used for grouping or categorization in a table, we need to create a summarized table that includes the necessary dimensions and calculated measures. This can be achieved using the SUMMARIZECOLUMNS function in DAX.

Here’s how you can create the summarized table:

DAX

SummarizeTable =

SUMMARIZECOLUMNS (

'Part Daily Details'[Part Number],

CalendarTable[Month-Year],

"Lemon Score", [Lemon Score],

"Category", [Lemon Score Category]

)

This DAX expression creates a new table that includes:

• Each unique Part Number
• Corresponding Month-Year from the calendar table
• The calculated Lemon Score measure
• The derived Lemon Score Category measure

This approach allows you to effectively categorize and analyze the Lemon Score data across different parts and time periods.