cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Vvelarde Community Champion
Community Champion

Re: Compare line chart

@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
Microsoft
Microsoft

Re: Compare line chart

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

bannedillo
Frequent Visitor

Re: Compare line chart

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!!!

Vvelarde Community Champion
Community Champion

Re: Compare line chart

@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

bannedillo
Frequent Visitor

Re: Compare line chart

Perfect!!! @Vvelarde

 

Many thanks for your help!

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors