Total Values in a column based on another column and display in a matrix visual

To create a matrix visual in Power BI that displays the total values in a column based on another column and compares them, you can follow these steps:

1. Data Modeling:

   • Ensure that you have your data imported into Power BI with the necessary columns, such as "Category Name," "Model Version #," and "Tons."

2. Create a Calculated Column:

   • You'll need to create a calculated column to identify the previous model version for each row. Use the following DAX formula to achieve this:
   DAXCopy code

   Previous Model Version = [Model Version #] - 1

   This calculated column will help you compare the current model version to the previous one.

3. Create a Matrix Visual:

   • Go to the Power BI report page where you want to display the matrix visual.

4. Configure the Matrix Visual:

   • In the Fields pane, drag and drop the following fields into the matrix visual:
     • Rows: Category Name
     • Columns: Model Version # (Sort ascending)
     • Values: Tons (This will give you the tonnage for the current version) and Previous Model Version (This will give you the tonnage for the previous version).

5. Calculate the Variance or % Change:

   • You can create a calculated column for the variance or % change between the current and previous versions using the following DAX formula:
   DAXCopy code

   Variance = VAR CurrentTons = [Tons] VAR PreviousTons = CALCULATE( SUM('YourTable'[Tons]), FILTER('YourTable', 'YourTable'[Model Version #] = [Previous Model Version]) ) RETURN IF(ISBLANK(PreviousTons), BLANK(), (CurrentTons - PreviousTons) / PreviousTons)

   Then, add the "Variance" column to the Values section of the matrix visual.

Now, your matrix visual in Power BI should display the steel tonnage for the current and previous model versions, along with the variance or % change between them, for each "Category Name." It will look something like this:

This matrix visual will effectively display the steel tonnage for the current and previous model versions, along with the variance or % change, grouped by "Category Name."