Scenario:
Suppose we have a Data Model as follows.
Suppose we have a Data Model as follows.
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.
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.
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]:
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.