DAX Logic to get the Distinct Count and Sum with a Measure in Filter Context in Power BI
Scenario :
Suppose we have a Sales Details data with some calculated measures as shown below
NetSales = SUM(SalesDetails[Net_Sales])
NetUnits = SUM(SalesDetails[Units_Sold])
OrdersRange = SWITCH(TRUE(),[NetUnits]>=40,"High",[NetUnits]>=10,"Medium","Low")
DAX Logics for Distinct Count filtered with Measure:
From the above data set, we can find the Distinct Count of SalesOrders, with a Filter applied on the existing calculated Measure [OrdersRange] as follows..
CountHighRangeOrders =
COUNTROWS(FILTER(VALUES(SalesDetails[SalesOrder_Id]),[OrdersRange] ="High"))
CountLowRangeOrders =
COUNTROWS(FILTER(VALUES(SalesDetails[SalesOrder_Id]),[OrdersRange] ="Low"))
DAX Logics for Sum of the Rows filtered with Measure :
We can also find the Sum of the Distinct Rows using the below DAX Logics
SumHighRangeOrders =
CALCULATE([NetSales],FILTER(VALUES(SalesDetails[SalesOrder_Id]),
[OrdersRange] ="High" ))
SumLowRangeOrders = CALCULATE([NetSales],FILTER(VALUES(SalesDetails[SalesOrder_Id]),
[OrdersRange] ="Low" ))
Scenario :
Suppose we have a Sales Details data with some calculated measures as shown below
NetSales = SUM(SalesDetails[Net_Sales])
NetUnits = SUM(SalesDetails[Units_Sold])
OrdersRange = SWITCH(TRUE(),[NetUnits]>=40,"High",[NetUnits]>=10,"Medium","Low")
DAX Logics for Distinct Count filtered with Measure:
From the above data set, we can find the Distinct Count of SalesOrders, with a Filter applied on the existing calculated Measure [OrdersRange] as follows..
CountHighRangeOrders =
COUNTROWS(FILTER(VALUES(SalesDetails[SalesOrder_Id]),[OrdersRange] ="High"))
CountLowRangeOrders =
COUNTROWS(FILTER(VALUES(SalesDetails[SalesOrder_Id]),[OrdersRange] ="Low"))
DAX Logics for Sum of the Rows filtered with Measure :
We can also find the Sum of the Distinct Rows using the below DAX Logics
SumHighRangeOrders =
CALCULATE([NetSales],FILTER(VALUES(SalesDetails[SalesOrder_Id]),
[OrdersRange] ="High" ))
SumLowRangeOrders = CALCULATE([NetSales],FILTER(VALUES(SalesDetails[SalesOrder_Id]),
[OrdersRange] ="Low" ))
Result :
--------------------------------------------------------------------------------------------------------
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.