Forum Discussion
Repeat Selected Value Across Rows In Measure
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
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!
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.
6 Replies
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:-
HTHPiHi 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
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.
Hi daniel_baciu , sure!
Can you open this link? https://drive.google.com/file/d/1eccjYD3Ushtie-pAEULjQTI743iQuPCS/view?usp=share_link
Pi
Hi PiEye - no, i cannot, but i have requested it using the Request access button.
Have got the request & granted access. Let me know if you run into any more issues
