Forum Discussion

New_be's avatar
New_be
Helper V
4 years ago
Solved

Return values from previous parameters

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...
Need Help
Show and Tell
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    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.

Seanan's avatar
Seanan
Solution Supplier
4 years ago

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.