topic Repeat Selected Value Across Rows In Measure in DAX Commands and Tips

Repeat Selected Value Across Rows In Measure

daniel_baciu — Tue, 07 Mar 2023 11:30:09 GMT

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#M41528

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!

Re: Repeat Selected Value Across Rows In Measure

PiEye — Wed, 08 Mar 2023 19:45:39 GMT

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

Re: Repeat Selected Value Across Rows In Measure

daniel_baciu — Thu, 09 Mar 2023 16:20:22 GMT

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

Re: Repeat Selected Value Across Rows In Measure

PiEye — Fri, 10 Mar 2023 08:08:55 GMT

Hi daniel_baciu , sure!

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

Re: Repeat Selected Value Across Rows In Measure

daniel_baciu — Tue, 14 Mar 2023 16:37:24 GMT

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

Re: Repeat Selected Value Across Rows In Measure

PiEye — Tue, 14 Mar 2023 20:49:51 GMT

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

Re: Repeat Selected Value Across Rows In Measure

daniel_baciu — Mon, 20 Mar 2023 13:02:42 GMT

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.