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.
Hello,
I´m trying to get the average number of a week, using this DAX-Formula:
CALCULATE(AVERAGEX(table;[Share]);ALLEXCEPT(Datetable;Datetable[Last 4 weeks]))
The problem is that some values are 0, and those should not be calculated. Therefore I tried this:
CALCULATE(AVERAGEX(table;[Share]);ALLEXCEPT(Datetable;Datetable[Last 4 weeks]);FILTER(VALUES(table);[share]=0)).
With this formula I´m getting blanks whenever the share was 0.
What I need:
The average of a week, calculated without 0 or blanks, and I want to see the average also in the 0/blank colums.
I´d appreciate any help.
Solved! Go to Solution.
In DAX, AVERAGE()/AVERAGEX() function will ingore entries with empty values. In your scenario, to ingore 0 values as well, you just need to put ALLEXCEPT() into FILTER() function. See my sample below:
Avg Per Week = CALCULATE ( AVERAGE ( Table1[Sales] ), FILTER ( ALLEXCEPT ( Table1, Table1[Week] ), Table1[Sales] <> 0 ) )
Regards,
In DAX, AVERAGE()/AVERAGEX() function will ingore entries with empty values. In your scenario, to ingore 0 values as well, you just need to put ALLEXCEPT() into FILTER() function. See my sample below:
Avg Per Week = CALCULATE ( AVERAGE ( Table1[Sales] ), FILTER ( ALLEXCEPT ( Table1, Table1[Week] ), Table1[Sales] <> 0 ) )
Regards,
im trying to make a similar measure to this but instead of numbers as in the "sales" column in this thread im counting instances of a specific phase. ex: if an employee completed 5 "units" in a week, the raw data will list each of those units in its own row with the date and employee name. im using a date table connected to the raw data table to show those 5 units were completed in "week 2" using the date in the raw data. ive attachd pictues of the table i have showing each employee and how many unis they completed per week. this table also shows weeks they did not complete anything. i do not want these "zero" weeks to count in the average. ive also attached a picture of part of the raw data and a picture of what columns im using from the raw data to creat the table.
You have to FILTER for ISBLANK;
1. Filter for your selected columns;
2. inside the selectcolumns use ALL that is SELECTED,
3. Calculate the AVGX, averagebyrow, as this is a rollup.
4. AND it cannot be ISBLANK.
FILTER ( SELECTCOLUMNS...( ALLSELECTED... ( CALCULATE... ( AVGX... (
AND ( NOT ( ISBLANK... ( Known[X] ) ), NOT ( ISBLANK ...( Known[Y] ) ) )
Or you can state NOT EQUAL to ZERO; but in this case the AVGX Function will still COUNT, SUM, and DIVIDE the total rows. So ignoring the Integer ZERO will not work.
but heres the formula anyway:
CALCULATE(
COUNTROWS( someTABLE),
ALLEXCEPT( someTABLE, someTABLE[SomeColumn] ), someTABLE[someCOLUMN] <> 0 )
Hi @kbrewer,
Your question is not clear. Please show a dataset and the expected result.
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |