Forum Discussion

bannedillo's avatar
bannedillo
Frequent Visitor
9 years ago
Solved

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 colum...
Need Help
v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

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's avatar
bannedillo
Frequent Visitor
9 years ago

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