Repeat Selected Value Across Rows In Measure

[daniel_baciu]

Hi,

The ultimate goal i have is to update the "Daily rate" page in the pbix model attached here, as in the picture below - ie insert the black lines and the red lines which are the yearly and the quarterly daily rates. Achieving this goal, i was thinking i need a measure for yearly average and quarterly average, hence the workaround below.

This is what i need:

This is what i currently have:

Trying to get the sum of the year on every period in a matrix, where the field for the Period and Year are not part of the data table, but of the calendar table. I am able to get the right data using the fields ("Period" / "Year") in the Data Table (see below the right hand side), but i need to link it to Calendar table ("Fiscal Period" / " Fiscal Year") as there i have various ways of presenting the data (ie "Jan 2020") (left hand side below image). Calendar table and Data table are joined via a Bridge table (which may cause the issue...?) using column "Period Qtr Year".

You'll find the formulas i was using in the pbix. I need the right formulas for "Value FY repeat" and "Value QTR repeat", and hopefully, i could use those 2 measures in the Daily rate chart to represent the yearly and the quarterly averages as in the first picture above.

As i cannot attached the pbix model, you can download it from here https://easyupload.io/o1qif7 - password is demopbix.

Related topics (below) i tried without succes but don't think those solutions work as they don't have 2 tables, but only one data table:

https://community.powerbi.com/t5/Desktop/Repeat-result-sum-on-all-rows/m-p/2577331

https://community.powerbi.com/t5/DAX-Commands-and-Tips/How-repeat-SUM-for-each-ROW/m-p/1917346#M4152...

You'll find 2 data tables ("Data Orders & Sales Demo" and "Data All Demo"). I need both as i have pages linked to both data sources. For tis exercise, we only use data from "Data All Demo" table. You will also notice a lot of measures with errors, that's because i deleted the real tables and replaced them with demo ones.

Any help is appreciated, as i was spending too much time on this already and could not afford any more unfortunately.

Many thanks in advance!

[daniel_baciu · Accepted solution]

Many thanks you for the solution @PiEye !

Your proposed formulas were not exactly what i needed, but i player around a bit on them and i found the right ones.

For Full Year this is what worked for me (i had to add " 'Dim Calendar' " to the "ALL()" expression):

Value FY repeat 6 = 
CALCULATE(
    [Value All Data k$ MTD Demo Daily],
    all('Dim Calendar'),
    'Dim Calendar'[Fiscal Year] = SELECTEDVALUE('Dim Calendar'[Fiscal Year])
)

 For QTR same (i had to add " 'Dim Calendar'[Fiscal Period] " to the "ALL()" expression):

Value QTR repeat 7 = 
CALCULATE(
    [Value All Data k$ MTD Demo Daily],
    all('Dim Calendar'[Fiscal Period]),
    'Dim Calendar'[Fiscal Quarter] = SELECTEDVALUE('Dim Calendar'[Fiscal Quarter])
)

I have now the same values on the absolute numbers which i could then divide to get the right Daily numbers. 

[PiEye]

Hi @daniel_baciu , sure!

Can  you open this link? https://drive.google.com/file/d/1eccjYD3Ushtie-pAEULjQTI743iQuPCS/view?usp=share_link

Pi

[daniel_baciu]

Hi @PiEye  - no, i cannot, but i have requested it using the Request access button.

[PiEye]

Have got the request & granted access. Let me know if you run into any more issues

[PiEye]

Hi @daniel_baciu 

Is this the sort of thing that you need?

Year total = CALCULATE([Value All Data k$ MTD Demo Daily],all(),'Dim Calendar'[Fiscal Year]=SELECTEDVALUE('Dim Calendar'[Fiscal Year]))

Qtr total = CALCULATE([Value All Data k$ MTD Demo Daily],all(),'Dim Calendar'[Fiscal Quarter]=SELECTEDVALUE('Dim Calendar'[Fiscal Quarter]))

These return the aggregation of  [Value All Data k$ MTD Demo Daily] for the relevant year or quarter, even if this field is not in the same visual:-

HTH

Pi 

[daniel_baciu]

Hi @PiEye ,

Many thanks for your input.

Can you also share your updated model? Trying to add a measure using your suggested formula is pointing an error in my model (A function 'SELECTEDVALUE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.)

Daniel