Repeat Selected Value Across Rows In Measure

[imranamikhan]

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

[imranamikhan]

@amitchandak  - There was an issue with my data model and the solution now works. Thank you

[amitchandak · Accepted solution]

@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