Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hello everyone,
I want to make an average per day of the production of each Item then sum it, BUT I want to remove te last day of production per item.
I Want to remove the last day of the average because if i don´t do so i will make me average go under the real number.
Here under I have a simple example of it (The red numbers are the numbers I want removed.
Date | Product 1 | Product 2 | Product 3 | |
01/01/2018 | 5000 | 100 | 50 | |
02/01/2018 | 5000 | 100 | 50 | |
03/01/2018 | 5000 | 100 | 25 | |
04/01/2018 | 5000 | 100 | ||
05/01/2018 | 5000 | 100 | ||
06/01/2018 | 5000 | 100 | ||
07/01/2018 | 2500 | 100 | ||
08/01/2018 | 50 | |||
09/01/2018 | Average | |||
Average | 5000 | 100 | 50 | 1716,667 |
The table in the database that I have presents 3 colums, Product / Date / Value
Product | Date | Value |
Product 1 | 01/01/2018 | XXX |
Product 2 | 02/01/2018 | XXX |
Product 3 | 03/01/2018 | XXX |
Product 1 | 04/01/2018 | XXX |
Product 2 | 05/01/2018 | XXX |
Product 3 | 06/01/2018 | XXX |
Product 1 | 07/01/2018 | XXX |
Product 2 | 08/01/2018 | XXX |
Product 3 | 09/01/2018 | XXX |
Product 3 | 10/01/2018 | XXX |
Thanks in regards
Hello everyone, thanks for the answer.
I tried to make the following DAX Formula:
AVERAGEX(
KEEPFILTERS(VALUES('TABLE'[Date]);
CALCULATE(MEASURE)
And it worked really fine, the problem is that doesnt filter the last day....
Did you try my technique to get rid of the last day in your calculation?
Greg!
I did your technique, and almost worked!
The problem is, is making a average of each line instead of the average per day.
In the example below, it should give me on day 01 the value of 50, and day 2 a number of 100.
I think is something with KEEPFILTERS, but I can´t get it right...
Date | Product | Value |
01/01/2018 | Product 1 | 25 |
01/01/2018 | Product 1 | 25 |
01/02/2018 | Product 1 | 25 |
01/02/2018 | Product 1 | 25 |
01/02/2018 | Product 1 | 25 |
01/02/2018 | Product 1 | 25 |
Thanks!
Hi @Anonymous,
Assuming that you have the data sample like below.
Please filter rows like below in Query Editor and then apply and close.
Then you could create a calculated column with the formula like this.
Column = VAR a = CALCULATE ( MAX ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[Product] ) ) RETURN IF ( 'Table'[Date] = a, BLANK (), 'Table'[Value] )
Here is the output.
Best Regards,
Cherry
You should be able to do something like:
Measure = VAR __product = MAX([Product]) VAR __table = FILTER('Table',[Product]=__product) VAR __max = MAXX(__table,[Date]) VAR __table1 = FILTER(__table,[Date]<>__max) RETURN AVERAGEX(__table1,[Value])
This assumes that the data in Power BI is in the 2nd example posted.
User | Count |
---|---|
145 | |
71 | |
69 | |
53 | |
52 |
User | Count |
---|---|
208 | |
94 | |
64 | |
60 | |
57 |