I need to supress the rows where the column sub-total has no value.

[AshishKS]

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

[Anonymous]

Hi @AshishKS ,

This is not possible, it means that both your column and row attributes will be gone. But the others are still populated with data, so it will be blank.

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.