Return values from previous parameters

[New_be]

Hi expert,

I have an issue with one of my report. Currently what I am doing in my report is like in table (1) below. I divided all RSS Value with PRD value (4,046.60).

But, what I really want is like in table (2) to get my desired percentage:
   if RSS=1, then it will return PRD Value, 4,046.60
   else if RSS= 2, then it will return Value of RSS 1 which is 3,028.08
   else if RSS= 3, then it will return Value of RSS 2 which is 2,025.34

*RSS column is whole number datatype

Big thanks for your help 🤝

[v-zhangti · Accepted solution]

Hi, @New_be 

You can try the following methods.

Measure = 
Var N1=MAXX(FILTER(ALL('Table'),[RSS]<SELECTEDVALUE('Table'[RSS])),[RSS])
Var N2=CALCULATE(SUM('Table'[VALUE]),FILTER(ALL('Table'),[RSS]=N1))
return
IF(SELECTEDVALUE('Table'[RSS])=1,SELECTEDVALUE('Table'[PRD Value]),N2)

In order to get Total to output the correct value, the IF(HASONEVALUE()) function is also required.

Pro = IF(HASONEVALUE('Table'[RSS]),[Measure],
SUMX(SUMMARIZE('Table',[RSS],"Pro",[Measure]),[Pro]))

Rercentage = DIVIDE(SUM('Table'[VALUE]),[Pro])

Does this match the output you expect?

Best Regards,

Community Support Team _Charlotte

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

[New_be]

Thanks! it works!

[Seanan]

Hi @New_be 

First create a calculated column with the following code:

Rank = RANKX(ALL(PRD),'PRD'[RSS],,ASC,Dense)

Then create another calculated column:

PreviousPRD = 
VAR CurrentIndex = 'PRD'[Rank]
VAR PreviousIndex = CALCULATE(MAX('PRD'[Rank]),FILTER('PRD','PRD'[Rank] < CurrentIndex),ALL('PRD'[Rank]))
VAR Result = CALCULATE(MAX('PRD'[Values]),FILTER('PRD','PRD'[Rank] = PreviousIndex))
return IF(Result = BLANK(),FIRSTNONBLANKVALUE('PRD'[PRD],'PRD'[PRD]),Result)

Finally create a 3rd calculated column

Divide = DIVIDE('PRD'[Values],'PRD'[PreviousPRD])

Result: 

When you add the Divide column, don't forget to change the data format to percentage.

Kind regards,
Seanan
If this post helped, please consider accepting it as the solution.

[New_be]

Hi sir @amitchandak , can you please help me with this problem?