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.
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
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])
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
35 | |
32 | |
18 | |
18 |