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

[jaybears130]

This is my first time posting here but I have a table of data containing the type of steel item (i'm in construction) and the total tonnage for each model version that I upload to power bi.  The steel values sometimes change based on the model version.  I'm trying to create a matrix visual that will show me the steel tonnage for the current version compared to the tonnage of the previous version.  For example, if I look at my matrix and look in the column for model version 8 I should see two columns - one that shows the tonnage for model version 8 and one that shows the tonnage for model version 7 (the most previous version); the column for 7 should show 7 and 6; the column for version 6 should show 6 and 5; so on and so forth.  I'd also like a column to show the variance or the % change between the two model versions.  I can't seem to come up with anything.  Here is the visual that's not working:

  

Below is the table I built containing me "test" data.  The real data comes from a 3D model and is a bit "heavier" than this simple spreadsheet but this was the best way I could quickly test different measures, etc.  I can't seem to figure out how to attach the actual spreadsheet, sorry.

Category Name	Model Version #	Tons
Structural Framing	1	5050
Structural Columns	1	2729
Structural Framing	2	5054
Structural Columns	2	2729
Structural Framing	3	5164
Structural Columns	3	2678
Structural Framing	4	5490
Structural Columns	4	2674
Structural Framing	5	5494
Structural Columns	5	2674
Structural Framing	6	5407
Structural Columns	6	2792
Structural Framing	7	5541
Structural Columns	7	2906
Structural Framing	8	5623
Structural Columns	8	2906

[Atif2917]

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:

scssCopy code

+------------------------+------------------------+------------------------+ | Category Name | Model Version # | Model Version # | +------------------------+------------------------+------------------------+ | | 1 | 2 | +------------------------+------------------------+------------------------+ | Structural Framing | 5050 | 5054 | | | (Previous Model Tons) | (Previous Model Tons) | +------------------------+------------------------+------------------------+ | Structural Columns | 2729 | 2729 | | | (Previous Model Tons) | (Previous Model Tons) | +------------------------+------------------------+------------------------+ | | 3 | 4 | +------------------------+------------------------+------------------------+ | Structural Framing | 5164 | 5490 | | | (Previous Model Tons) | (Previous Model Tons) | +------------------------+------------------------+------------------------+ | Structural Columns | 2678 | 2674 | | | (Previous Model Tons) | (Previous Model Tons) | +------------------------+------------------------+------------------------+ | | 5 | 6 | +------------------------+------------------------+------------------------+ | Structural Framing | 5494 | 5407 | | | (Previous Model Tons) | (Previous Model Tons) | +------------------------+------------------------+------------------------+ | Structural Columns | 2674 | 2792 | | | (Previous Model Tons) | (Previous Model Tons) | +------------------------+------------------------+------------------------+ | | 7 | 8 | +------------------------+------------------------+------------------------+ | Structural Framing | 5541 | 5623 | | | (Previous Model Tons) | (Previous Model Tons) | +------------------------+------------------------+------------------------+ | Structural Columns | 2906 | 2906 | | | (Previous Model Tons) | (Previous Model Tons) | +------------------------+------------------------+------------------------+

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

[andhiii079845 · Accepted solution]

Hello,

perhaps this help you, its a measure

priv =
VAR __version =
    SELECTEDVALUE ( Tabelle1[Model Version #] )
VAR __cat =
    SELECTEDVALUE ( Tabelle1[Category Name] )
RETURN
    IF (
        HASONEFILTER ( Tabelle1[Category Name] ),
        SUMX (
            FILTER (
                ALL ( Tabelle1 ),
                Tabelle1[Model Version #] = __version - 1
                    && Tabelle1[Category Name] = __cat
            ),
            Tabelle1[Tons]
        ),
        SUMX (
            FILTER ( ALL ( Tabelle1 ), Tabelle1[Model Version #] = __version - 1 ),
            Tabelle1[Tons]
        )
    )

[jaybears130]

THANK YOU!!!!!!!!!!!!!!!!