Saturday, September 30, 2023

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
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog