Forum Discussion
Highlight 7 consecutive Increases or Decreases
Hi Anonymous
Greetings!
This is the continuity post for the solution you have provided on Solved: Re: Highlight 7th data point based on consecutive ... - Microsoft Fabric Community
Now, I'm calling Value column in y axis (displays in %) where the data for value column stored in decimal data type. and this data is fetched based on another column where the column unit ="%". ex: Calculate(Sum(value), unit="%"). and possible value foramts in Value column are 0.80, 0.50, 0.90 so on. so we need to fetch the data from Value column only whre the Unit column = "%". This will dispay the data in value column as 0.80 , 0.50,0.90 which is equals to 80%, 50%, 90%.
I tried replacing the code with the logics you provided but using this data format ,y axis is not highlighting the 7th consecutive increase or decreases. I've attached the power bi file where page 1 displays the data highlights as expected with your logic using total orders .
In Page 2- I've tried replicating the same logic with % column(value column with decimal type) but the highlight is not working. The value field is not called in consecutive measures in the attached power bi file but I tried replacing the value column there but the logic was not working. So seeking your help on acheiving the same result given in page 1 to be applied in Page 2 as well (of course with different column with different data type & format). and consider the scenarios listed below under Logics to be considered note.
Thanks in advance for your help!
Testfile_7thConsecutiveNumberHighlightPoint_Test.pbix
Logics to be considered:
- We should bring top 20 recent batches only based on MFR Date - Created measure and working fine
- Need to use Value field in Y axis which is in the type of decimal number where it contains the value for %. and have to use Unit = '%' to get the percentage data for value column.
- Use batch id in x axis and Value field in Y axis - (0 to 100%). In this 20 latest batches highlight 7 consecutive increase or decreases.
2 Replies
Hi Anonymous ,
Please modify these two measures.Consecutive_Decreasing = VAR a = MAXX ( FILTER ( ALLSELECTED ( 'Batch Table' ), [BatchID] = MAX ( 'Batch Table'[BatchID]--modify ) - 1 ), [Total Value] --modify ) VAR b = IF ( a <> BLANK (), a-[Total Value] ) VAR c = MINX ( ALLSELECTED ( 'Batch Table' ), [BatchID] ) RETURN IF ( b > 0 || MAX ( 'Batch Table'[BatchID] ) = c, 1, 0 )VAR a = MAXX ( FILTER ( ALLSELECTED ( 'Batch Table' ), [BatchID] = MAX ( 'Batch Table'[BatchID]--modify ) - 1 ), [Total Value]--modify ) VAR b = IF ( a <> BLANK (), [Total Value] - a ) VAR c = MINX ( ALLSELECTED ( 'Batch Table' ), [BatchID] ) RETURN IF ( b > 0 || MAX ( 'Batch Table'[BatchID] ) = c, 1, 0 )
The final result is as follows, hopefully it will meet your needs.Please see the attached pbix for reference.
Best Regards,
Dengliang Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi Anonymous Yes this looks good. Thanks for your quick response. But for my data (different data) , the logic is not working.
The total value should sum the totals for the latest 20 batches only. I corrected the logic to work in that way.
But its not highlighting the points correctly. I'm pretty sure I'm missing something.
Could you pls. check on the measures below & the table and line screenshots provided after this for decreasing data points and help me on fixing this issue?
The logic I used:
BatchRank =RANKX(Filter( ALLSELECTED(BATCH_DATA), NOT(ISBLANK(BATCH_DATA[manufacturing_date]))),CALCULATE(MAX(BATCH_DATA[manufacturing_date])), ,DESC,Dense)Total Value =CALCULATE(SUM(BATCH_PARAMETER[value] // Summing the 'value' column in BATCH_PARAMETER))//Total value is in decimal number formatConsecutive_Decreasing =Var d = MAXX ( FILTER ( ALLSELECTED ( 'BATCH_DATA' ),[BatchRank]<=20),BATCH_DATA[batch_number])VAR a =CALCULATE( MAXX(FILTER(ALLSELECTED('BATCH_PARAMETER'),'BATCH_PARAMETER'[batch_number] =CALCULATE(VALUES('BATCH_DATA'[batch_number]),'BATCH_DATA'[batch_number] = d - 1)),CALCULATE( sumx(FILTER(ALLSELECTED('BATCH_PARAMETER'),[BatchRank]<=20),[Total Value]))))VAR b =IF ( a <> BLANK (), CALCULATE( sumx(FILTER(ALLSELECTED('BATCH_PARAMETER'),[BatchRank]<=20),[Total Value]))-a )VAR c =MINX ( ALLSELECTED (BATCH_DATA ), [batch_number] )RETURN IF ( b > 0 || d = c, 1, 0 )Decreasing_flag =VAR a =MAXX (FILTER (ALLSELECTED ( 'BATCH_DATA' ),[batch_number] <= MAX ( 'BATCH_DATA'[batch_number] ) && [Consecutive_Decreasing]= 0), [batch_number])VAR b =IF (a = BLANK (),MAX ( BATCH_DATA[batch_number] ) - MINX ( ALLSELECTED ( 'BATCH_DATA'[batch_number] ), [batch_number] ) + 1,MAX ( BATCH_DATA[batch_number] ) - a + 1)RETURNIF ( b <> 0 && MOD ( b, 7 ) = 0, 1, 0 )pls. refer to the image below:
Line chart: