How to create a Summary of Products with No Sales using DAX in Power BI

How to use SUMMARIZECOLUMNS Function to create a Summary of Products with No Sales in Power BI DAX

Scenario:
Suppose we have a Data Model as follows:

The relationships are as follows:

In this Data Model, we may know that some of the Products does not have the sales in some specific Months.

Sales = Sum(fct_Orders_Sample[Net_Sales])

Now we want to the generate the Summary of Products with No Sales. We can achieve this Scenario using DAX as follows:

Prod_No_Sales_Summary =

VAR _YearFilter =

    FILTER( VALUES(dim_Calendar[DimYear]),

        dim_Calendar[DimYear] IN {2020})

VAR _MonthFilter =

    FILTER(VALUES(dim_Calendar[MonthName]),

        dim_Calendar[MonthName] IN {"Jan","Feb", "Mar"})

VAR _ProdFilter =

    FILTER( VALUES(dim_Products[Product_ID]),

        NOT(ISBLANK(dim_Products[Product_ID])))

VAR _Summary =

FILTER(

    SUMMARIZECOLUMNS(

        dim_Calendar[DimYear],

        dim_Calendar[MonthName],

        dim_Products[Product_ID],

        dim_Products[Product_Name],

        _YearFilter,         _MonthFilter,

        _ProdFilter,       

        /* "NoSales", ISBLANK([Sales]) */

        "NoSales", ISBLANK(COUNTROWS(fct_Orders_Sample))

      ),

    [NoSales]=TRUE()

)

RETURN

_Summary

Result:

Please not that I have filtered the result only for specific months.

---------------------------------------------------------------------------

We can also achieve the above result using following version of DAX:

VAR _YearFilter =

    FILTER(

        VALUES(dim_Calendar[DimYear]),

        dim_Calendar[DimYear] IN {2020}

    )

VAR _ProdFilter =

    FILTER(

        VALUES(dim_Products[Product_ID]),

        NOT(ISBLANK(dim_Products[Product_ID]))

    )

VAR _Summary =

    CALCULATETABLE(

        SUMMARIZECOLUMNS(

            dim_Calendar[DimYear],

            dim_Calendar[MonthName],

            dim_Products[Product_ID],

            dim_Products[Product_Name],

            _YearFilter,

            _ProdFilter,

            "NoSales", IF(ISBLANK(COUNTROWS(fct_Orders_Sample)), TRUE(), FALSE())

        ),

        KEEPFILTERS (dim_Calendar[MonthName] IN {"Jan", "Feb", "Mar"})

    )

RETURN

    FILTER (_Summary, [NoSales] = TRUE())

Result:

---------------------------------------------------------------------------

We can also create a summary of Products with and without Sales as per below:

Prod_Sales_Summary =

VAR _YearFilter =

    FILTER( VALUES(dim_Calendar[DimYear]),

        dim_Calendar[DimYear] IN {2020})

VAR _MonthFilter =

    FILTER(VALUES(dim_Calendar[MonthName]),

        dim_Calendar[MonthName] IN {"Jan","Feb", "Mar"})

VAR _ProdFilter =

    FILTER( VALUES(dim_Products[Product_ID]),

        NOT(ISBLANK(dim_Products[Product_ID])))

VAR _Summary =

CALCULATETABLE(

    ADDMISSINGITEMS(

     /* Select Columns used in the Summary */       

        dim_Calendar[DimYear],

        dim_Calendar[MonthName],

        dim_Products[Product_ID],

        dim_Products[Product_Name],

        SUMMARIZECOLUMNS(

            dim_Calendar[DimYear],

            dim_Calendar[MonthName],

            dim_Products[Product_ID],

            dim_Products[Product_Name],

            _YearFilter,

            _MonthFilter,

            _ProdFilter,  

            "NetSales", [Sales],

            "NoSalesFlag", ISBLANK(COUNTROWS(fct_Orders_Sample))*1

            ),

    
        /* Group by Columns used in the Select */   

        dim_Calendar[DimYear],

        dim_Calendar[MonthName],

        dim_Products[Product_ID],

        dim_Products[Product_Name]

        ),

    KEEPFILTERS(_YearFilter),

    KEEPFILTERS(_MonthFilter),

    KEEPFILTERS(_ProdFilter)

    )

RETURN

_Summary

Result:

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------