Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
daniel_baciu
Helper I
Helper I

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:

2023-02-07 10_16_48-Window.png

This is what i currently have:

2023-03-07 12_12_01-Sales Co Dashboards_4_Daily Rate Chart_demo - Power BI Desktop.png

 

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".

 

2023-03-07 12_13_28-Sales Co Dashboards_4_Daily Rate Chart_demo - Power BI Desktop.png

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!

1 ACCEPTED SOLUTION
daniel_baciu
Helper I
Helper I

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. 

2023-03-20 13_59_41-Window.png

View solution in original post

6 REPLIES 6
daniel_baciu
Helper I
Helper I

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. 

2023-03-20 13_59_41-Window.png

PiEye
Resolver II
Resolver II

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

PiEye
Resolver II
Resolver II

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:-

PiEye_0-1678225263470.png

 

 
HTH
 
Pi 
 
 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors