Forum Discussion
Zack92
Helper III
1 year agoHelp Needed – Grand Total Not Showing in Matrix Table
Hello, I need a bit of help with getting the Grand Total to show up at the bottom of my Matrix table. I created a custom measure to display the view I need, but for some reason, the total isn’t ...
- 1 year ago
Hi,
Please check the below picture and the attached pbix file whether it solves the issue.
Selected Metric Value = SUMX ( VALUES ( Metrics[Metric] ), CALCULATE ( SWITCH ( SELECTEDVALUE ( Metrics[Metric] ), "Revenue", SUM ( nl_Sales[ext_price] ) / 1000, "Volume", SUM ( nl_Sales[inv_qty] ), "Gross Profit $", SUM ( nl_Sales[gp$] ) / 1000, "Gross Margin %", IF ( [Gross Profit $] / [Revenue] > 1 || [Gross Profit $] / [Revenue] < 0, BLANK (), [Gross Profit $] / [Revenue] ) * 100, BLANK () ) ) )YoY % Change = VAR CurrentValue = SUMX ( VALUES ( Metrics[Metric] ), CALCULATE ( SWITCH ( TRUE (), SELECTEDVALUE ( Metrics[Metric] ) = "Revenue", [Revenue], SELECTEDVALUE ( Metrics[Metric] ) = "Volume", [Volume], SELECTEDVALUE ( Metrics[Metric] ) = "Gross Profit $", [Gross Profit $], SELECTEDVALUE ( Metrics[Metric] ) = "Gross Margin %", [Gross Margin %], BLANK () ) ) ) VAR LastPeriodValue = SUMX ( VALUES ( Metrics[Metric] ), CALCULATE ( SWITCH ( TRUE (), ISFILTERED ( 'Date'[Date].[Quarter] ) || ISFILTERED ( 'Date'[Date].[Month] ), CALCULATE ( SWITCH ( TRUE (), SELECTEDVALUE ( Metrics[Metric] ) = "Revenue", [Revenue], SELECTEDVALUE ( Metrics[Metric] ) = "Volume", [Volume], SELECTEDVALUE ( Metrics[Metric] ) = "Gross Profit $", [Gross Profit $], SELECTEDVALUE ( Metrics[Metric] ) = "Gross Margin %", [Gross Margin %], BLANK () ), PARALLELPERIOD ( 'Date'[Date], -1, QUARTER ) ), ISFILTERED ( 'Date'[Date].[Year] ), CALCULATE ( SWITCH ( TRUE (), SELECTEDVALUE ( Metrics[Metric] ) = "Revenue", [Revenue], SELECTEDVALUE ( Metrics[Metric] ) = "Volume", [Volume], SELECTEDVALUE ( Metrics[Metric] ) = "Gross Profit $", [Gross Profit $], SELECTEDVALUE ( Metrics[Metric] ) = "Gross Margin %", [Gross Margin %], BLANK () ), SAMEPERIODLASTYEAR ( 'Date'[Date] ) ), BLANK () ) ) ) RETURN IF ( ISBLANK ( LastPeriodValue ), BLANK (), - DIVIDE ( CurrentValue - LastPeriodValue, LastPeriodValue, 0 ) * 100 )
Jihwan_Kim
Super User
1 year agoHi,
Please check the below picture and the attached pbix file whether it solves the issue.
Selected Metric Value =
SUMX (
VALUES ( Metrics[Metric] ),
CALCULATE (
SWITCH (
SELECTEDVALUE ( Metrics[Metric] ),
"Revenue", SUM ( nl_Sales[ext_price] ) / 1000,
"Volume", SUM ( nl_Sales[inv_qty] ),
"Gross Profit $", SUM ( nl_Sales[gp$] ) / 1000,
"Gross Margin %",
IF (
[Gross Profit $] / [Revenue] > 1
|| [Gross Profit $] / [Revenue] < 0,
BLANK (),
[Gross Profit $] / [Revenue]
) * 100,
BLANK ()
)
)
)
YoY % Change =
VAR CurrentValue =
SUMX (
VALUES ( Metrics[Metric] ),
CALCULATE (
SWITCH (
TRUE (),
SELECTEDVALUE ( Metrics[Metric] ) = "Revenue", [Revenue],
SELECTEDVALUE ( Metrics[Metric] ) = "Volume", [Volume],
SELECTEDVALUE ( Metrics[Metric] ) = "Gross Profit $", [Gross Profit $],
SELECTEDVALUE ( Metrics[Metric] ) = "Gross Margin %", [Gross Margin %],
BLANK ()
)
)
)
VAR LastPeriodValue =
SUMX (
VALUES ( Metrics[Metric] ),
CALCULATE (
SWITCH (
TRUE (),
ISFILTERED ( 'Date'[Date].[Quarter] ) || ISFILTERED ( 'Date'[Date].[Month] ),
CALCULATE (
SWITCH (
TRUE (),
SELECTEDVALUE ( Metrics[Metric] ) = "Revenue", [Revenue],
SELECTEDVALUE ( Metrics[Metric] ) = "Volume", [Volume],
SELECTEDVALUE ( Metrics[Metric] ) = "Gross Profit $", [Gross Profit $],
SELECTEDVALUE ( Metrics[Metric] ) = "Gross Margin %", [Gross Margin %],
BLANK ()
),
PARALLELPERIOD ( 'Date'[Date], -1, QUARTER )
),
ISFILTERED ( 'Date'[Date].[Year] ),
CALCULATE (
SWITCH (
TRUE (),
SELECTEDVALUE ( Metrics[Metric] ) = "Revenue", [Revenue],
SELECTEDVALUE ( Metrics[Metric] ) = "Volume", [Volume],
SELECTEDVALUE ( Metrics[Metric] ) = "Gross Profit $", [Gross Profit $],
SELECTEDVALUE ( Metrics[Metric] ) = "Gross Margin %", [Gross Margin %],
BLANK ()
),
SAMEPERIODLASTYEAR ( 'Date'[Date] )
),
BLANK ()
)
)
)
RETURN
IF (
ISBLANK ( LastPeriodValue ),
BLANK (),
- DIVIDE ( CurrentValue - LastPeriodValue, LastPeriodValue, 0 ) * 100
)