Forum Discussion

imranamikhan's avatar
imranamikhan
Icon for Helper V rankHelper V
5 years ago
Solved

Repeat Selected Value Across Rows In Measure

Hi everyone,

 

Per the example below, I have the following table which displays the following columns:

 

  • Period Numbers (1-12)
  • Projects Number
  • Value

 

You will notice above that each project has data for every Period.

 

I have a separate custom table (created in Power Query) which displays each distinct period. The Value column is the result of a measure which returns the associated value for each project per the relevant period.

 



Problem:

I have another column (Selected Period Value) which should return the associated value for the selected period in a slicer and selected project. This value should repeat across all rows. This is to allow me to run another separate calculation (Difference).

 

In PowerBI, I have attempted to use the SELECTEDVALUE function to return the selected period as a paramater in a measure. But this results in Blank data being returned.

 

Selected Period = SELECTEDVALUE('REF_Programme Financials Unpivot'[Sheet Number (Period)], BLANK())

 

Value = CALCULATE (
SUM ( 'SP_XL_Programme Financials'[Current Authority] ),
FILTER ( ALL ( 'SP_XL_Programme Financials'[Sheet Number (Period)] ), 'SP_XL_Programme Financials'[Sheet Number (Period)] = [Selected Period]),
FILTER ( ALL ( 'SP_XL_Programme Financials'[Project No.] ), 'SP_XL_Programme Financials'[Project No.] = [Selected Project] )
)

 

 

 

If I hardcode the selected value into the measure, the measure returns the required results.
 
Value if Hardcoded = CALCULATE (
SUM ( 'SP_XL_Programme Financials'[Current Authority] ),
FILTER ( ALL ( 'SP_XL_Programme Financials'[Sheet Number (Period)] ), 'SP_XL_Programme Financials'[Sheet Number (Period)] = 12),
FILTER ( ALL ( 'SP_XL_Programme Financials'[Project No.] ), 'SP_XL_Programme Financials'[Project No.] = [Selected Project] )
)


Could anyone please advise how I should approach and resolve this?

 

Best regards,

AmiK

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    imranamikhan , In such cases it better to have separate period table and use that.

     

    As per me these two mesures should work

     

    Value = SUM ( 'SP_XL_Programme Financials'[Current Authority] )

    value selected = calculate([Values], filter(All(Period), Period[period] =selectedvalue(Period[period])))

     

    then you can take diff

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    imranamikhan , In such cases it better to have separate period table and use that.

     

    As per me these two mesures should work

     

    Value = SUM ( 'SP_XL_Programme Financials'[Current Authority] )

    value selected = calculate([Values], filter(All(Period), Period[period] =selectedvalue(Period[period])))

     

    then you can take diff