The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 |
Solved! Go to Solution.
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]
)
)
Proud to be a Super User!
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:
Data Modeling:
Create a Calculated Column:
This calculated column will help you compare the current model version to the previous one.
Create a Matrix Visual:
Configure the Matrix Visual:
Calculate the Variance or % Change:
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."
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]
)
)
Proud to be a Super User!
THANK YOU!!!!!!!!!!!!!!!!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
93 | |
81 | |
63 | |
56 |
User | Count |
---|---|
247 | |
122 | |
110 | |
77 | |
72 |