Monday, December 30, 2019

How to get the Distinct Count and Sum with Measure as a Filter in Power BI

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

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.