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

[SelviPrabhu]

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:

BatchID	TotalOrders
10001	100
10002	90
10003	80
10004	70
10005	60
10006	40
10007	20
10008	101
10009	200
10010	100
10011	200
10012	2
10013	226
10014	555
10015	555
10016	959
10017	666
10018	845
10019	212
10020	21

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!

[Anonymous · Accepted solution]

Hi,

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

hello @SelviPrabhu , 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.

[SelviPrabhu]

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?

[SelviPrabhu]

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

[rajendraongole1]

Hi @SelviPrabhu - 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

[SelviPrabhu]

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?

[SelviPrabhu]

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?

[SelviPrabhu]

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!

[rajendraongole1]

Hi @SelviPrabhu - can you please check the attached pbix file. 

Hope this works well.