Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bannedillo
Frequent Visitor

Compare line chart

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.

1 ACCEPTED SOLUTION

@bannedillo

 

Hi, review this PBIX. Let me know if that is what you want.

 

https://drive.google.com/file/d/0B95C8CKdTZE3YjNzaVl4c0ZzeGM/view?usp=sharing

 

hours.png

 

 

 

 




Lima - Peru

View solution in original post

4 REPLIES 4
v-huizhn-msft
Employee
Employee

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.
EXAMPLE_CHART.png
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!!!

@bannedillo

 

Hi, review this PBIX. Let me know if that is what you want.

 

https://drive.google.com/file/d/0B95C8CKdTZE3YjNzaVl4c0ZzeGM/view?usp=sharing

 

hours.png

 

 

 

 




Lima - Peru

Perfect!!! @Vvelarde

 

Many thanks for your help!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.