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 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 🤝

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.

4 Replies

  • 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.

  • v-zhangti's avatar
    v-zhangti
    Community Support
    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.