How to perform Bottom N Analysis by Measure in Power BI

How to Analyze Bottom N Products by Selected Measure in Power BI

Scenario:
Suppose we have a Data Model as follows.

The Relationships are as follows:

Based on this Model, we need to calculate the Bottom N Products with the Low Sales and Low Cost.

The Bottom 3 Products need to display dynamically in selected Measure (Sales or Cost) area.

We can achieve this scenario as explained in the below steps.

1) Create the below Static Tables to use as Slicers:

The following are the static and disconnected tables from the Data Model. They are only using as Slicers.

MsrSelector : To use as Slicer to Switch the Measure Dynamically.

BottomN_Selector : To use as a Slicer to choose the no. of Bottom N items.

2) Create the required base Measures:

The following are the Main measures, based on which we analyze the Bottom N Products.

Net Sales = SUMX ( tbl_Sales, ( tbl_Sales[Unit_Price] * tbl_Sales[Units_Sold] ))

Total Cost = SUMX ( tbl_Sales,( tbl_Sales[Units_Sold] * tbl_Sales[Unit_Cost] ))

The following is the Switch measure is used to choose the base measure dynamically.

SelectedMsr =

    SWITCH( TRUE(),

        SELECTEDVALUE(MsrSelector[MsrName])="Total Cost", [Total Cost],

        SELECTEDVALUE(MsrSelector[MsrName])="Net Sales", [Net Sales],

        BLANK()

        )

3) Create the Bottom N Measures:
The following measure will returns the Bottom N Products, across the Region and the Month in the selected Measure ( [Net Sales] or [Total Cost] ) area.

BotN_Msr_Prod_By_Reg_Month(Calc) =

VAR vBotN = SELECTEDVALUE(BottomN_Selector[BotN])

VAR vDataset =

    FILTER(

            CROSSJOIN(

                ALLSELECTED(tbl_Products[Prod_Name]),

                ALLSELECTED(tbl_Region[Region_Name])

                ),

        [SelectedMsr] <> BLANK()

        )

VAR vBotN_Prod = TOPN(vBotN, vDataset, [SelectedMsr], ASC )

VAR vLowProdSales=CALCULATE( [SelectedMsr],KEEPFILTERS(vBotN_Prod))

RETURN

    IF(vLowProdSales<>BLANK(),vLowProdSales)

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

The above DAX formula can also be written in the following way:

BotN_Msr_Prod_By_Reg_Month(SumX) =

VAR vBotN = SELECTEDVALUE(BottomN_Selector[BotN])

VAR vDataset =

    FILTER(

        CROSSJOIN(

            ALLSELECTED(tbl_Products[Prod_Name]),

            ALLSELECTED(tbl_Region[Region_Name])

            ),

        [SelectedMsr] <> BLANK()

        )

VAR vBotN_Prod = TOPN(vBotN, vDataset, [SelectedMsr], ASC )

VAR vLowProdSales= SUMX( KEEPFILTERS(vBotN_Prod), [SelectedMsr] )

RETURN

    IF(vLowProdSales<>BLANK(),vLowProdSales)

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

The following DAX formula return the Concatenated list of Bottom N Products in the selected measure area.

BotN_Products_By_Reg_Month =

VAR vBotN = SELECTEDVALUE(BottomN_Selector[BotN])

VAR vMsrArea = SELECTEDVALUE(MsrSelector[MsrName])

VAR vDataset =

    FILTER(

        CROSSJOIN(

            ALLSELECTED(tbl_Products[Prod_Name]),

            ALLSELECTED(tbl_Region[Region_Name])),

        [SelectedMsr] <> BLANK()

        )

VAR vBotN_Prod = TOPN(vBotN, vDataset, [SelectedMsr], ASC )

VAR vLowSaleProd=

    "[ Measure Area: " & vMsrArea & " ]" & UNICHAR(10) & "( Products: " &

    CONCATENATEX( KEEPFILTERS(vBotN_Prod), [Prod_Name], " | ", [SelectedMsr], ASC )

    & " )"

RETURN

    IF([SelectedMsr]<>BLANK(),vLowSaleProd)

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

Output:

The Bottom 3 Products with low [Net Sales]:

The Bottom 3 Products with low [Total Cost]:

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

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

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