I need to supress the rows where the column sub-total has no value.
The show items with No data works, if I have not formatted the Amounts in my columns, but once I format it using DAX, this option doesn't work anymore. This one is w/o formatting the amount column This one is after formatting the Amount Columns(Month-Year) My DAX for Visual 2 - What can I add here to suppress rows which have no data in the month columns! Actuals = VAR _selectedHeader = SELECTEDVALUE ('a360_finance fin_PnL_Template2'[Level_1]) VAR _Actuals = FORMAT([Actual Amount],"#,##0.00;(#,##0.00)") VAR _IsLevel2Visible = ISINSCOPE('a360_finance fin_PnL_Template2'[Level_2]) VAR _DisplayDetailCode = SELECTEDVALUE('a360_finance fin_PnL_Template2'[Highlight]) VAR _Revenue = CALCULATE([Actual Amount], REMOVEFILTERS('a360_finance fin_PnL_Template2'), 'a360_finance fin_PnL_Template2'[Level_1] IN {"Sales Revenue"} ) VAR _COGS = CALCULATE([Actual Amount], REMOVEFILTERS('a360_finance fin_PnL_Template2'), 'a360_finance fin_PnL_Template2'[Level_1] IN {"Cost of Products Sold"} ) VAR _CM = CALCULATE([Actual Amount], REMOVEFILTERS('a360_finance fin_PnL_Template2'), 'a360_finance fin_PnL_Template2'[Level_1] IN {"Sales Revenue","Cost of Products Sold"} ) VAR _OCGS = CALCULATE([Actual Amount], REMOVEFILTERS('a360_finance fin_PnL_Template2'), 'a360_finance fin_PnL_Template2'[Level_1] IN {"Other COGS"} ) VAR _SGA = CALCULATE([Actual Amount], REMOVEFILTERS('a360_finance fin_PnL_Template2'), 'a360_finance fin_PnL_Template2'[Level_1] IN {"SGA"} ) VAR _NOPEXP = CALCULATE([Actual Amount], REMOVEFILTERS('a360_finance fin_PnL_Template2'), 'a360_finance fin_PnL_Template2'[Level_1] IN {"Non Operating"} ) VAR _Results = SWITCH(TRUE(), _IsLevel2Visible = TRUE() && _DisplayDetailCode = 1, Blank(), _selectedHeader = "Revenue", FORMAT(_Revenue, "#,##0.00;(#,##0.00)"), _selectedHeader = "Cost of Goods Sold", FORMAT(_COGS,"#,##0.00;(#,##0.00)"), _selectedHeader = "Contribution Margin", FORMAT(_CM,"#,##0.00;(#,##0.00)"), _selectedHeader = "Contribution Margin Percentage", FORMAT((_CM/_Revenue), "#0.0%;(#0.0%)"), _selectedHeader = "Other Cost of Goods Sold", FORMAT(_OCGS,"#,##0.00;(#,##0.00)"), _selectedHeader = "Total COGS", FORMAT(_COGS+_OCGS,"#,##0.00;(#,##0.00)"), _selectedHeader = "Gross Profit", FORMAT(_Revenue+_COGS+_OCGS,"#,##0.00;(#,##0.00)"), _selectedHeader = "Gross Profit Percentage", FORMAT((_Revenue+_COGS+_OCGS)/_Revenue,"#0.0%;(#0.0%)"), _selectedHeader = "Total SGA", FORMAT(_SGA,"#,##0.00;(#,##0.00)"), _selectedHeader = "Total Operating Expenses", FORMAT(_SGA,"#,##0.00;(#,##0.00)"), _selectedHeader = "Operating Income", FORMAT((_Revenue+_COGS+_OCGS+_SGA),"#,##0.00;(#,##0.00)"), _selectedHeader = "Operating Income Percentage", FORMAT((_Revenue+_COGS+_OCGS+_SGA)/_Revenue,"#0.0%;(#0.0%)"), _selectedHeader = "Total Non Operating", FORMAT(_NOPEXP,"#,##0.00;(#,##0.00)"), _selectedHeader = "Net Income", FORMAT((_Revenue+_COGS+_OCGS+_SGA+_NOPEXP),"#,##0.00;(#,##0.00)"), _Actuals //FORMAT(_Actuals, "#,##0.00;(#,##0.00)") ) Return _Results
