Forum Discussion

LR's avatar
LR
Frequent Visitor
8 years ago

Automatically count the number of consecutive points decreasing / increasing

Hi Team ! I would like to highlight areas on a graph where 6 consecutive points (or more) are increasing or decreasing. How to perform such calculation ?  Regards       ...
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi LR,


Unfortunately, current charts not support use multiple values and legend at same time. I don't think you can use that method to achieve your requirement.

 

Can you please share some sample file to test and coding formula?

 

Regards,
Xiaoxin Sheng

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    HI LR,

     

    As I said, you can simply use below column formula to mark tags of each records.

     

     

    Category =
VAR previous =
    MAXX (
        FILTER (
            ALL ( Table1 ),
            [Type] = EARLIER ( [Type] )
                && [Date] < EARLIER ( [Date] )
        ),
        [Values]
    )
VAR result = [Values] - previous
RETURN
    IF (
        previous <> BLANK (),
        IF ( result > 0, "increasing", IF ( result < 0, "decreasing" ) )
    )

     

     

    BTW, current power bi not support multiple legends or combine use legend fields and multiple value fields, so I think you can't use these columns to create graph in power bi .

     

    Regards,
    Xiaoxin Sheng

    • LR's avatar
      LR
      Frequent Visitor
      8 years ago

       

      Hi Anonymous,



      I am sorry for going out of time to answer.

       

      Your formula tells me if a point is increasing or decreasing compare to the one before by type.

      I noticed in the table below that some lines are not completed.
      The fact that the first line is empty is normal in my opinion. But why are there others ?
      I don't think that is due to equal values ​​because these lines are well completed in the column.
      You can see it in the picture below, at the end of Type B.


          

      Now, if we look at the graph, I put in color the parts said to be "decreasing".
      We see that sometimes there are increasing points considered as decreasing (it is also visible in the table)...


      To get the column "Count decreasing" in the table I went through an intermediate step.
      I filled the clolonne of 1 when Category = "decreasing".
      Only, for the final phase (ie. to count the number of consecutive points that are decreasing, respectively increasing) I have to formulate it in some way in my formula. Do you have ideas of how to do it?

      Thank you very much for your time and your answers,

      Kind Regards,
      LR