Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
jaybears130
Frequent Visitor

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

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:

  

jaybears130_0-1696614337015.png

 

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 NameModel Version #Tons
Structural Framing15050
Structural Columns12729
Structural Framing25054
Structural Columns22729
Structural Framing35164
Structural Columns32678
Structural Framing45490
Structural Columns42674
Structural Framing55494
Structural Columns52674
Structural Framing65407
Structural Columns62792
Structural Framing75541
Structural Columns72906
Structural Framing85623
Structural Columns82906

 

 

1 ACCEPTED SOLUTION
andhiii079845
Super User
Super User

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]
        )
    )

andhiii079845_0-1696619844868.png

 

 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
Atif2917
New Member

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
Super User
Super User

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]
        )
    )

andhiii079845_0-1696619844868.png

 

 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.