Forum Discussion
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
4 Replies
- HotChilli
Community Champion
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..
- tarunsingla
Solution Sage
Measure 3 = DIVIDE(SUM(Table1[Sale]), SUMX(FILTER(Table1, Table1[Sale] <> BLANK()), Allocation))
https://docs.microsoft.com/en-us/dax/sumx-function-dax#example
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])
