Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Highlight 7th data point based on consecutive increases or decreases in line chart in PowerBI

Hi There,

I've Batch table where I'm having Batch ID and Total Orders and the data format will looks like as given in table below:

Batch Table:

BatchIDTotalOrders
10001100
1000290
1000380
1000470
1000560
1000640
1000720
10008101
10009200
10010100
10011200
100122
10013226
10014555
10015555
10016959
10017666
10018845
10019212
1002021

 

In Line Chart = X axis = Batch ID , Y Axis = Total Orders.

Now I need to highlight the 7th data point (7th batch id) only if there is 7  consecutive/continuous increases or decreases in the data point. In the image below , you can see first 7 data points are consecutively decreasing , now I need to highlight that 7 th data point alone. 

I've tried  creating Measures but measures for ConsecutiveDecrease / ConsecutiveIncrease always returns 0 where as Expected value for ConsecutiveDecrease measure for 7th point is 1.

 

ConsecutiveDecrease =
VAR CurrentID = MAX('Batch Table'[BatchID])
VAR PreviousValues =
    FILTER(
        'Batch Table',
        'Batch Table'[BatchID] <= CurrentID && 'Batch Table'[BatchID] > CurrentID - 7
    )
VAR DecreaseCount =
    COUNTROWS(
        FILTER(
            PreviousValues,
            'Batch Table'[TotalOrders] <
            CALCULATE(
                MAX('Batch Table'[TotalOrders]),
                FILTER('Batch Table', 'Batch Table'[BatchID] = EARLIER('Batch Table'[BatchID]) +1)
            )
        )
    )
RETURN
    IF(DecreaseCount = 6, 1, 0)
 
ConsecutiveIncrease =
VAR CurrentID = MAX('Batch Table'[BatchID])
VAR PreviousValues =
    FILTER(
        'Batch Table',
        'Batch Table'[BatchID] <= CurrentID && 'Batch Table'[BatchID] > CurrentID - 7
    )
VAR IncreaseCount =
    COUNTROWS(
        FILTER(
            PreviousValues,
            'Batch Table'[TotalOrders] >
            CALCULATE(
                MAX('Batch Table'[TotalOrders]),
                FILTER('Batch Table', 'Batch Table'[BatchID] = EARLIER('Batch Table'[BatchID]) +1)
            )
        )
    )
RETURN
    IF(IncreaseCount = 6, 1, 0)
 
HighlightPoint =
IF(
   [ConsecutiveIncrease]=1 ||  [ConsecutiveDecrease] = 1,
   1,
   0
)
 
Pls. help on deriving the solution / solving this issue. Thanks in Advance!
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi,

    Thanks for the solution rajendraongole1  offered,and i want to offer some more infotmation for user to refer to.

    hello Anonymous , you can refer to the following sample.

    Sample data 

    Create the following measures.

    _Decreasing =
VAR a =
    MAXX (
        FILTER ( ALLSELECTED ( 'Table' ), [BatchID] = MAX ( [BatchID] ) - 1 ),
        [TotalOrders]
    )
VAR b =
    IF ( a <> BLANK (), a - SUM ( 'Table'[TotalOrders] ) )
VAR c =
    MINX ( ALLSELECTED ( 'Table' ), [BatchID] )
RETURN
    IF ( b > 0 || MAX ( 'Table'[BatchID] ) = c, 1, 0 )

    _Increment =
VAR a =
    MAXX (
        FILTER ( ALLSELECTED ( 'Table' ), [BatchID] = MAX ( [BatchID] ) - 1 ),
        [TotalOrders]
    )
VAR b =
    IF ( a <> BLANK (), SUM ( 'Table'[TotalOrders] ) - a )
VAR c =
    MINX ( ALLSELECTED ( 'Table' ), [BatchID] )
RETURN
    IF ( b > 0 || MAX ( 'Table'[BatchID] ) = c, 1, 0 )

    Decreasing_flag =
VAR a =
    MAXX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            [BatchID] <= MAX ( 'Table'[BatchID] )
                && [_Decreasing] = 0
        ),
        [BatchID]
    )
VAR b =
    IF (
        a = BLANK (),
        MAX ( 'Table'[BatchID] ) - MINX ( ALLSELECTED ( 'Table'[BatchID] ), [BatchID] ) + 1,
        MAX ( 'Table'[BatchID] ) - a + 1
    )
RETURN
    IF ( b <> 0 && MOD ( b, 7 ) = 0, 1, 0 )

    increasing_flag =
VAR a =
    MAXX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            [BatchID] <= MAX ( 'Table'[BatchID] )
                && [_Increment] = 0
        ),
        [BatchID]
    )
VAR b =
    IF (
        a = BLANK (),
        MAX ( 'Table'[BatchID] ) - MINX ( ALLSELECTED ( 'Table'[BatchID] ), [BatchID] ) + 1,
        MAX ( 'Table'[BatchID] ) - a + 1
    )
RETURN
    IF ( b <> 0 && MOD ( b, 7 ) = 0, 1, 0 )

    Color_flag= IF([Decreasing_flag]=1||[increasing_flag]=1,1,0)

    Then create a line chart visual first, and put the related field to the visual. and sort the visual ascending by BarchID, open the marker.

    Then click the viusal, then change it to clustered column visual.

    Then put the color_flag measure to the column color format.

     

     

    Then click the visual , change it to line chart visual again.

    Output

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

     

     

     

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi,

    Thanks for the solution rajendraongole1  offered,and i want to offer some more infotmation for user to refer to.

    hello Anonymous , you can refer to the following sample.

    Sample data 

    Create the following measures.

    _Decreasing =
VAR a =
    MAXX (
        FILTER ( ALLSELECTED ( 'Table' ), [BatchID] = MAX ( [BatchID] ) - 1 ),
        [TotalOrders]
    )
VAR b =
    IF ( a <> BLANK (), a - SUM ( 'Table'[TotalOrders] ) )
VAR c =
    MINX ( ALLSELECTED ( 'Table' ), [BatchID] )
RETURN
    IF ( b > 0 || MAX ( 'Table'[BatchID] ) = c, 1, 0 )

    _Increment =
VAR a =
    MAXX (
        FILTER ( ALLSELECTED ( 'Table' ), [BatchID] = MAX ( [BatchID] ) - 1 ),
        [TotalOrders]
    )
VAR b =
    IF ( a <> BLANK (), SUM ( 'Table'[TotalOrders] ) - a )
VAR c =
    MINX ( ALLSELECTED ( 'Table' ), [BatchID] )
RETURN
    IF ( b > 0 || MAX ( 'Table'[BatchID] ) = c, 1, 0 )

    Decreasing_flag =
VAR a =
    MAXX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            [BatchID] <= MAX ( 'Table'[BatchID] )
                && [_Decreasing] = 0
        ),
        [BatchID]
    )
VAR b =
    IF (
        a = BLANK (),
        MAX ( 'Table'[BatchID] ) - MINX ( ALLSELECTED ( 'Table'[BatchID] ), [BatchID] ) + 1,
        MAX ( 'Table'[BatchID] ) - a + 1
    )
RETURN
    IF ( b <> 0 && MOD ( b, 7 ) = 0, 1, 0 )

    increasing_flag =
VAR a =
    MAXX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            [BatchID] <= MAX ( 'Table'[BatchID] )
                && [_Increment] = 0
        ),
        [BatchID]
    )
VAR b =
    IF (
        a = BLANK (),
        MAX ( 'Table'[BatchID] ) - MINX ( ALLSELECTED ( 'Table'[BatchID] ), [BatchID] ) + 1,
        MAX ( 'Table'[BatchID] ) - a + 1
    )
RETURN
    IF ( b <> 0 && MOD ( b, 7 ) = 0, 1, 0 )

    Color_flag= IF([Decreasing_flag]=1||[increasing_flag]=1,1,0)

    Then create a line chart visual first, and put the related field to the visual. and sort the visual ascending by BarchID, open the marker.

    Then click the viusal, then change it to clustered column visual.

    Then put the color_flag measure to the column color format.

     

     

    Then click the visual , change it to line chart visual again.

    Output

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi Anonymous Great yaar! This works. Thank you so much! rajendraongole1  Thanks to you as well for helping me on this! Thank You both !!!

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi Anonymous Out of curiosity, how shall we highlight the next data point (8th data point) instead of highlighting the 7th data point if 7 consecutive decreasing or increasing found?  I understood the logics being used in all 4 measures and I was adjusting the _decrement & _increment measures to display 1 to the very next Id of 7th consective decreasing/increasing batch id. But ending up in invalid data only. could you pls. provide solution on this?

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    1 year ago

    Hi Anonymous - The issue in your current approach is that Earlier is used in a way that might not give the intended comparison between the current and previous values across consecutive rows

    first lets create a measure for Consecutive Decreases

    ConsecutiveDecrease =
    VAR CurrentID = MAX('Batch Table'[BatchID])
    VAR ConsecutiveDecreaseCount =
    SUMX(
    FILTER(
    'Batch Table',
    'Batch Table'[BatchID] >= CurrentID - 6 && 'Batch Table'[BatchID] <= CurrentID
    ),
    IF(
    'Batch Table'[TotalOrders] <
    CALCULATE(
    MAX('Batch Table'[TotalOrders]),
    FILTER('Batch Table', 'Batch Table'[BatchID] = 'Batch Table'[BatchID] - 1)
    ),
    1,
    0
    )
    )
    RETURN
    IF(ConsecutiveDecreaseCount = 6, 1, 0)

     

    Similarly, the following measure will count consecutive increases by checking the previous values

    ConsecutiveIncrease =
    VAR CurrentID = MAX('Batch Table'[BatchID])
    VAR ConsecutiveIncreaseCount =
    SUMX(
    FILTER(
    'Batch Table',
    'Batch Table'[BatchID] >= CurrentID - 6 && 'Batch Table'[BatchID] <= CurrentID
    ),
    IF(
    'Batch Table'[TotalOrders] >
    CALCULATE(
    MAX('Batch Table'[TotalOrders]),
    FILTER('Batch Table', 'Batch Table'[BatchID] = 'Batch Table'[BatchID] - 1)
    ),
    1,
    0
    )
    )
    RETURN
    IF(ConsecutiveIncreaseCount = 6, 1, 0)

     

    you can use both the ConsecutiveDecrease and ConsecutiveIncrease measures in a final HighlightPoint measure that will return 1

     

    HighlightPoint =
    IF(
    [ConsecutiveIncrease] = 1 || [ConsecutiveDecrease] = 1,
    1,
    0

    )

    in your visualization line chart add the highlight and values measure and in conditional formatting make sure that highlight point is 1.

     

    I hope this works for 7 consecutive increases or decreases

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi rajendraongole1 But again it shows me 0 at 7th data point. not 1. Pls. refer the second page where it shows 0 for 7th data point and in first page at 7th data points its not highlighting the data value in red color. Thanks in Advance!

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi rajendraongole1 Now I can see 1 at the total level only. I've applied the conditional formatting at HighlightPoint measure level if One then Red color and for 0's Blue. You can see all of the values are displaying as blue only where as HighlightPoint value for BatchId 10007 should be displayed in red colour because its 7th continuos decreasing point. Since its having the values as 0 instead of 1 this point also displaying blue color. Could you pls. check and help on this?

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi rajendraongole1 BatchId is in INT format and in Table visual I'm not aggregating it. Is that causing this issue? I need to use this BatchId in X axis in Line chart so it should be converted to Text format. If I covert BatchId into text then getting below issue on the measure becuase we are applying math operations on batchid. so we need to apply conversions in our measure I guess. Could you pls. verify on this?