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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
GianlucaM
Helper II
Helper II

consider all values in a table but show just a selected value

Hi everyone,

 

i've created some measure that gives me back the weighted average of the incoming order as well as the weighted average of my invoices in order to give me back the book to bill ratio.

the issue is that this calculation exist only if I keep all the weeks in my table, in other words I cannot extract a singe value for one week (the last one for example) as the model this way consider just the selected week messing up the concept of weighted average.

Is there a way I can use to display (like in a card for example) a single value for one week, without compromizing the calculation itself?
this is the table how it worlk properly: i want to extract and show just a line.

GianlucaM_0-1693215272621.png

Thanks
for reference this is how I calculate the weighted average 

WA12 Booking  Val = var Myindex= max('Calendar'[Week_offset])
var Mywindow=12
return
        (calculate(sum('Backlog-order income'[Value]),filter(ALLSELECTED('Calendar'),
                                        'Calendar'[Week_offset]<=Myindex&&'Calendar'[Week_offset]>Myindex-Mywindow))
                                                                    /if([Nr data rows]<Mywindow,if([Nr data rows]=0,1,[Nr data rows]),(Mywindow)))
6 REPLIES 6
v-yiruan-msft
Community Support
Community Support

Hi @GianlucaM ,

I'm not clear about your requirement. Do you want to display one row on the visual? But what's the calculation logic? Is there any relationship created between the table 'Backlog-order income' and 'Calendar'? Could you please provide some source data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

 

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi,

what  I need is to display in a card or somethig external to the table where the measure is created, a single value of the same.
I'll attach here a simplified files, in order to better understand: i've created this measure based on the week offset in order to have a progression of the value i'm managing: this value is the weighted average of the incoming order divided by the average of the billed goods on the same week, this to provide the famous book to bill ratio. This number exist just in the table (or visual) where the measure is placed and extracted has no sense as it will loose his logic (basically the date to define the correct weighted average). On the other hand I would like to focus on the value of the last week of this index without having to bring with me all the historical data. How to do this?
Moreover focusing on just the last value i'm able to use other Dimensions to evaluate the same measure over the last week, and for example see which country has the higher or the lower value of this KPI
https://drive.google.com/file/d/1CjqQQ4ge2PNRfXbUfcl3beiw9YTcwV3A/view?usp=drive_link 

Hi @GianlucaM ,

I didn't have the access to your shared file. Could you please grant the proper permission to it? Thank you.

vyiruanmsft_0-1693786978936.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yiruan-msft did you manage to check?

 

Anyone can help here?

@v-yiruan-msft is this link fine for you? is it working?

Hi,

 

I've granted you the acces, let me know if this work

Regards

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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