Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

How can I calculate the diff between two products from different tables and with a set of conditions

I've got two different types of diesel, each of them with different prices.

I've also got calculated Moving Averages of both, and I need to see the average GAP between them but under the condition they need to have a value in the same DAY to calculate such average, otherwise it wouldn't be valid. The tables and expected result is kind of as follows:

TABLE DIESEL TYPE A

DatePrice DIESEL TYPE A
01-feb1,2 
05-may1,3
06-ago1,09
06-ago1,1
07-sep1,5

 

TABLE DIESEL TYPE B

DatePrice DIESEL TYPE B
01-feb0,9
05-may1,05
06-ago0,8
06-ago0,75
12-nov0,7

 

Date Average A Average B
01-feb1,20,9
05-may1,31,05
06-ago1,0950,775
07-sep1,5-
12-nov-0,7

 

The expected GAP should be:

Date GAP Average
01-feb0,30
05-may0,25
06-ago0,32
07-sep-
12-nov-

In September 7th and November 12th I DONT want to have these averages calculated or shown on my graph, i.e. on my measure.

In summary, i wanna get an average of the difference between these two prices by date and under the condition there should be values for the same date in both type of diesels, otherwise I don't want to calcu

Need Help
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    try this?

     

    GapAvg = 
    IF(
        [Average A]<>BLANK() && [Average B]<>BLANK(),
        [Average A] - [Average B]
    )
  • TonyZhou1980's avatar
    TonyZhou1980
    3 years ago

    My solution is to create a measure as below, and create a date table to connect both table type A and type B.  Then you can configure the visual accordingly.

     
    Diff =
    var _avgA=average(TYPEA[Price DIESEL TYPE A])
    var _avgB=average(TYPEB[Price DIESEL TYPE B])
    return
        if(
            or(_avgA=BLANK(),_avgB=BLANK()),
            blank(),
            _avgA-_avgB
        )
     
     

4 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    try this?

     

    GapAvg = 
    IF(
        [Average A]<>BLANK() && [Average B]<>BLANK(),
        [Average A] - [Average B]
    )
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      EXACTLY THIS!!! 

       

      I've looking for this the whooooole day. I appreciate so much your help.

      • FreemanZ's avatar
        FreemanZ
        Icon for Super User rankSuper User
        3 years ago

        thank you as well. Not every suggestion is varified with real data by the advisor. So your timely feedback is extremely important. 

  • TonyZhou1980's avatar
    TonyZhou1980
    Icon for Resolver I rankResolver I
    3 years ago

    My solution is to create a measure as below, and create a date table to connect both table type A and type B.  Then you can configure the visual accordingly.

     
    Diff =
    var _avgA=average(TYPEA[Price DIESEL TYPE A])
    var _avgB=average(TYPEB[Price DIESEL TYPE B])
    return
        if(
            or(_avgA=BLANK(),_avgB=BLANK()),
            blank(),
            _avgA-_avgB
        )