Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I need help calculating a X year average and the yearly percent change in a Matrix visual where the values have been switched to rows.
As an example, here is the data that I am using:
When I import it to Power BI, I use the Matrix visual to pivot the GrossSales, NetSales, and Inventory columns so they are shown as rows with years running accross the top:
However, the request from the user is to have the three and five year average as additional columns, in addition to yearly percent changes as rows, so it would look like this:
Im stuck trying to write a measure that would do either of these. Any guidance would be greatly appreciated.
Solved! Go to Solution.
Hi @JC_Silva ,
According to your description, if you want to display the result exactly like yours, you should create tables contain all elements both for Axis and Column in the matrix. Here's my solution.
1.Unpivot the GrossSales, NetSales, and Inventory columns as you described, get the following table.
2.Create two new tables.
Category:
Row:
Make relationship like this:
3.Create a measure. This formula is a bit long because many values need to be defined separately. It can be roughly divided into three parts, original data, percentage and average.
Measure =
IF (
NOT ( MAX ( 'Row'[Row] ) IN { "3 Year Average", "5 Year Average" } )
&& MAX ( 'Category'[Category] ) IN VALUES ( 'Table'[Category] ),
SUM ( 'Table'[Value] ),
IF (
MAX ( 'Category'[Category] ) = "GrossSalesYearPercentChange"
&& NOT ( MAX ( 'Row'[Row] ) IN { "3 Year Average", "5 Year Average" } ),
IF (
MAX ( 'Row'[Row] ) = MINX ( ALL ( 'Row' ), 'Row'[Row] ),
BLANK (),
DIVIDE (
SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Year] = VALUE ( MAX ( 'Row'[Row] ) )
&& 'Table'[Category] = "Gross Sales"
),
'Table'[Value]
)
- SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Year]
= VALUE ( MAX ( 'Row'[Row] ) ) - 1
&& 'Table'[Category] = "Gross Sales"
),
'Table'[Value]
),
SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Year] = VALUE ( MAX ( 'Row'[Row] ) )
&& 'Table'[Category] = "Gross Sales"
),
'Table'[Value]
)
)
),
IF (
MAX ( 'Category'[Category] ) = "NetSalesYearPercentChange"
&& NOT ( MAX ( 'Row'[Row] ) IN { "3 Year Average", "5 Year Average" } ),
IF (
MAX ( 'Row'[Row] ) = MINX ( ALL ( 'Row' ), 'Row'[Row] ),
BLANK (),
DIVIDE (
SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Year] = VALUE ( MAX ( 'Row'[Row] ) )
&& 'Table'[Category] = "Net Sales"
),
'Table'[Value]
)
- SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Year]
= VALUE ( MAX ( 'Row'[Row] ) ) - 1
&& 'Table'[Category] = "Net Sales"
),
'Table'[Value]
),
SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Year] = VALUE ( MAX ( 'Row'[Row] ) )
&& 'Table'[Category] = "Net Sales"
),
'Table'[Value]
)
)
),
IF (
MAX ( 'Category'[Category] ) = "InventoryYearPercentChange"
&& NOT ( MAX ( 'Row'[Row] ) IN { "3 Year Average", "5 Year Average" } ),
IF (
MAX ( 'Row'[Row] ) = MINX ( ALL ( 'Row' ), 'Row'[Row] ),
BLANK (),
DIVIDE (
SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Year] = VALUE ( MAX ( 'Row'[Row] ) )
&& 'Table'[Category] = "Inventory"
),
'Table'[Value]
)
- SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Year]
= VALUE ( MAX ( 'Row'[Row] ) ) - 1
&& 'Table'[Category] = "Inventory"
),
'Table'[Value]
),
SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Year] = VALUE ( MAX ( 'Row'[Row] ) )
&& 'Table'[Category] = "Inventory"
),
'Table'[Value]
)
)
),
IF (
MAX ( 'Row'[Row] ) = "3 Year Average",
SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Category] = MAX ( 'Category'[Category] )
&& 'Table'[Year]
IN {
MAXX ( ALL ( 'Table' ), 'Table'[Year] ),
MAXX ( ALL ( 'Table' ), 'Table'[Year] ) - 1,
MAXX ( ALL ( 'Table' ), 'Table'[Year] ) - 2
}
),
'Table'[Value]
) / 3,
IF (
MAX ( 'Row'[Row] ) = "5 Year Average",
SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Category] = MAX ( 'Category'[Category] )
&& 'Table'[Year]
IN {
MAXX ( ALL ( 'Table' ), 'Table'[Year] ),
MAXX ( ALL ( 'Table' ), 'Table'[Year] ) - 1,
MAXX ( ALL ( 'Table' ), 'Table'[Year] ) - 2,
MAXX ( ALL ( 'Table' ), 'Table'[Year] ) - 3,
MAXX ( ALL ( 'Table' ), 'Table'[Year] ) - 4,
MAXX ( ALL ( 'Table' ), 'Table'[Year] ) - 5
}
),
'Table'[Value]
) / 5
)
)
)
)
)
)
Get the expected result.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @JC_Silva ,
According to your description, if you want to display the result exactly like yours, you should create tables contain all elements both for Axis and Column in the matrix. Here's my solution.
1.Unpivot the GrossSales, NetSales, and Inventory columns as you described, get the following table.
2.Create two new tables.
Category:
Row:
Make relationship like this:
3.Create a measure. This formula is a bit long because many values need to be defined separately. It can be roughly divided into three parts, original data, percentage and average.
Measure =
IF (
NOT ( MAX ( 'Row'[Row] ) IN { "3 Year Average", "5 Year Average" } )
&& MAX ( 'Category'[Category] ) IN VALUES ( 'Table'[Category] ),
SUM ( 'Table'[Value] ),
IF (
MAX ( 'Category'[Category] ) = "GrossSalesYearPercentChange"
&& NOT ( MAX ( 'Row'[Row] ) IN { "3 Year Average", "5 Year Average" } ),
IF (
MAX ( 'Row'[Row] ) = MINX ( ALL ( 'Row' ), 'Row'[Row] ),
BLANK (),
DIVIDE (
SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Year] = VALUE ( MAX ( 'Row'[Row] ) )
&& 'Table'[Category] = "Gross Sales"
),
'Table'[Value]
)
- SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Year]
= VALUE ( MAX ( 'Row'[Row] ) ) - 1
&& 'Table'[Category] = "Gross Sales"
),
'Table'[Value]
),
SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Year] = VALUE ( MAX ( 'Row'[Row] ) )
&& 'Table'[Category] = "Gross Sales"
),
'Table'[Value]
)
)
),
IF (
MAX ( 'Category'[Category] ) = "NetSalesYearPercentChange"
&& NOT ( MAX ( 'Row'[Row] ) IN { "3 Year Average", "5 Year Average" } ),
IF (
MAX ( 'Row'[Row] ) = MINX ( ALL ( 'Row' ), 'Row'[Row] ),
BLANK (),
DIVIDE (
SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Year] = VALUE ( MAX ( 'Row'[Row] ) )
&& 'Table'[Category] = "Net Sales"
),
'Table'[Value]
)
- SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Year]
= VALUE ( MAX ( 'Row'[Row] ) ) - 1
&& 'Table'[Category] = "Net Sales"
),
'Table'[Value]
),
SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Year] = VALUE ( MAX ( 'Row'[Row] ) )
&& 'Table'[Category] = "Net Sales"
),
'Table'[Value]
)
)
),
IF (
MAX ( 'Category'[Category] ) = "InventoryYearPercentChange"
&& NOT ( MAX ( 'Row'[Row] ) IN { "3 Year Average", "5 Year Average" } ),
IF (
MAX ( 'Row'[Row] ) = MINX ( ALL ( 'Row' ), 'Row'[Row] ),
BLANK (),
DIVIDE (
SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Year] = VALUE ( MAX ( 'Row'[Row] ) )
&& 'Table'[Category] = "Inventory"
),
'Table'[Value]
)
- SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Year]
= VALUE ( MAX ( 'Row'[Row] ) ) - 1
&& 'Table'[Category] = "Inventory"
),
'Table'[Value]
),
SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Year] = VALUE ( MAX ( 'Row'[Row] ) )
&& 'Table'[Category] = "Inventory"
),
'Table'[Value]
)
)
),
IF (
MAX ( 'Row'[Row] ) = "3 Year Average",
SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Category] = MAX ( 'Category'[Category] )
&& 'Table'[Year]
IN {
MAXX ( ALL ( 'Table' ), 'Table'[Year] ),
MAXX ( ALL ( 'Table' ), 'Table'[Year] ) - 1,
MAXX ( ALL ( 'Table' ), 'Table'[Year] ) - 2
}
),
'Table'[Value]
) / 3,
IF (
MAX ( 'Row'[Row] ) = "5 Year Average",
SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Category] = MAX ( 'Category'[Category] )
&& 'Table'[Year]
IN {
MAXX ( ALL ( 'Table' ), 'Table'[Year] ),
MAXX ( ALL ( 'Table' ), 'Table'[Year] ) - 1,
MAXX ( ALL ( 'Table' ), 'Table'[Year] ) - 2,
MAXX ( ALL ( 'Table' ), 'Table'[Year] ) - 3,
MAXX ( ALL ( 'Table' ), 'Table'[Year] ) - 4,
MAXX ( ALL ( 'Table' ), 'Table'[Year] ) - 5
}
),
'Table'[Value]
) / 5
)
)
)
)
)
)
Get the expected result.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you! This worked great!
Hi,
I can help you with calculating yearly percentage change (not the 3 and 5 year average). If you are OK with that, then share the link of the PBI file.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |