Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
bipro-atlanta
Frequent Visitor

DAX To filter data based on another measure

I am looking  to filter Measure based on value Present.  So in example below i am looking to average out based on only values sold .. For Example i have three rows of sales but 4 rows of allocation , but i should only use Sales with  Allocation . Which is 3 Rows of Sales should be divided by 3 rows of allocation instead of four.. Can you kindly suggest on how use a DAX ..  Filter Value where Sales is not blank / Allocation Where sales is not blank 

Filter.PNG

4 REPLIES 4
Anonymous
Not applicable

If you want to just display the value this will work for returning the correct total and only the products that contain a sale.

Count of Products with a Sale:= Countrows(Calculatetable('Sales', Not(ISBLANK('Sales'[Sale]))  * This may be altered, just not sure the grain of your data.  But this will return the table where the statement is true *
Allocation of Product w/ Sale: SUMX(Filter(Values('Sales'[Product]), NOT(ISBLANK([Count of Products with a Sale])),calculate(SUM('Sales'[Allocation])))

 

If you just want the grand totals to reflect that logic you will need to do an if statement on the product.  ISFiltered('Sales'[Prodcut])

HotChilli
Super User
Super User

If the allocation is always "1" then

Measure 3 = 
  DIVIDE(SUM(Table1[Sale]), COUNTROWS(FILTER(Table1, Table1[Sale] <> BLANK())))

Allocation is not always 1 .. It based on number of days selected in date range.. 

Measure 3 = DIVIDE(SUM(Table1[Sale]), SUMX(FILTER(Table1, Table1[Sale] <> BLANK()), Allocation))

https://docs.microsoft.com/en-us/dax/sumx-function-dax#example

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors