Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I want to compare a production scraps. So I have created an excel, where it reproduces every time there is an error in production.
So, the 3 first columns are the important columns. Date_time column, shows the Date of production and time, where event has taken, 1 error, 1 error, etc. And column 3 is the error it self.
For day 11/04/2017, will be in the same column, etc...
So I want to be able to show line chart comparision by day of production, showin the errors.
Example:
Axis X, time: from 8:30 to 17:30. Axis Y, count of errors, every time error occurs should count previous count + error number.
And lines color by day. So, for day 10/04/2017 at time 9:50 should line be at count 17.
I tried to use, Hour = TIMEVALUE(Table[datetime_field]) to generate new column with herarchy in date, one day shows ok, but next days are wrong.
Thanks In advance.
Solved! Go to Solution.
Hi, review this PBIX. Let me know if that is what you want.
https://drive.google.com/file/d/0B95C8CKdTZE3YjNzaVl4c0ZzeGM/view?usp=sharing
Hi @bannedillo,
Based on my understanding, please try the following steps.
1. Create a Rank column.
Rank=RANKX(Fact,Ract[Date_time],,ASC,Dense)
2. Use the LOOKUP function to get 7 days ago.
7 day ago = LOOKUPVALUE(Fact[Date_time],Fact[RANK],Fact[RANK]-7)
2. Create two measures to get CumulativeCount and 7 day ago CumulativeCount.
CumulativeCount= CALCULATE ( COUNT ( Fact[Event] ), FILTER ( ALLEXCEPT( Fact,Fact[Date_time] ), Fact[Time] <= MAX ( Fact[Time] ) ) )
7 day ago CumulativeCount=
CALCULATE (
COUNT ( Fact[Event] ),
FILTER (
ALLEXCEPT( Fact,Fact[7 day ago] ),
Fact[Time] <= MAX ( Fact[Time] )
)
)
3. Create a slicer including Fact[Date_time]. Create a line chart, select the [Time] as axis level, the two measure as value level.
When you select one day in slicer, it will return the cunrrent day and 7 day ago different time's count.
If this is not what you want, please share your sample data in document rather than picture which is different to copy and reproduce.
Best Regards,
Angelia
Many thanks for your help Angelia. @v-huizhn-msft
I manage to make all you said, in a different excel of what I am attaching here, but as I need to make it work with only 3 columns, since the Postgresql only have this 3 columns, I attached here the correct XLS.
For example, we have 4 days of production, in each day the system will register any error ocurring, and time of the error.
So the final target of this line chart, is to compare in real time every day, so if I compare 4 days, it has to be drawed 4 lines, into the chart.
In that example, Imagine, AXIS X is time, so 8:30 , 9:30 ,10:30 ,11:30, 12:30, 13:30, 14:30 , etc..... usually from 8:30 till 17:30. Just that, no scroll bar needed.
AXIS Y, quantity of event, in this case the line of each day has to be accumulative, as you can see in the example chart.
And, COLOR LINES, each color is a day, for example, RED; 06/04/2017, GREEN; 07/04/2017, BLUE; 10/04/2017, etc...
So we can check in real time how is it going the production of today compare to another days.
EXCEL SAMPLE and PBIX:
https:// mega.nz/fm/8r4mEQLa
http:// www100.zippyshare.com/v/slsMnMDy/file.html
https:// ufile.io/06862
(remove space)
Many thanks!!!
Hi, review this PBIX. Let me know if that is what you want.
https://drive.google.com/file/d/0B95C8CKdTZE3YjNzaVl4c0ZzeGM/view?usp=sharing
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |