Report cant bring data to a visual and times out

[Justas4478]

Hi, I have a report with simple table that is trying to load 12 months worth of data.
but I get this error in published version:

And this in the desktop:

I suspect it is fault of my measure.
The measure works well on small amount of data, but when it is more it starts to fail.
This is two part measure.
Part one:

Pieces Per Pick Cal = AVERAGEX('Product','Product'[Pieces Per Pick])

Part two:

Total Actual Eaches = SUMX('Outbound Delivery','Outbound Delivery'[Total Actual]*'Product'[Pieces Per Pick Cal])

I can't do calculated columns since company is using live connection model so I am forced to use only measures.
If anyone knows any solutions that could help, I would appreciated the suggestions.
Thanks

[v-csrikanth]

Hi @Justas4478 
Thanks for confimation let us know if you are facing any issues.
If the issue has been resolved, can you mark the post as resolved.
Please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!

Best Regards,
Cheri Srikanth

[v-csrikanth]

Hi @Justas4478 

I wanted to follow up since I haven't heard from you in a while. Have you had a chance to try the suggested solutions?
If your issue is resolved, please consider marking the post as solved. However, if you're still facing challenges, feel free to share the details, and we'll be happy to assist you further.
Looking forward to your response!

Best Regards,
Community Support Team _ C Srikanth.

[Justas4478]

@v-csrikanth Hi, it got really busy at work and I did not had time to test suggested solutions.
I will have time next week and will respond once I manage to try them.

[v-csrikanth · Accepted solution]

Hi @Justas4478 

Thanks for reaching out to the Fabric Community.
Sorry for the late response.
If you’re trying to get a row-specific value  then the key issue is relationship: Power BI needs a relationship from 'Outbound Delivery' to 'Product'.

You can then try this measure:

Total Actual Eaches =
SUMX(
    'Outbound Delivery',
    'Outbound Delivery'[Total Actual] *
    RELATED('Product'[Pieces Per Pick])
)

Use DAX Studio on the desktop report to measure query performance.

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.

[bhanu_gautam]

@Justas4478 , Try using

DAX
Pieces Per Pick Cal =
VAR AvgPiecesPerPick = AVERAGEX('Product', 'Product'[Pieces Per Pick])
RETURN AvgPiecesPerPick

Total Actual Eaches =
VAR PiecesPerPickCal = [Pieces Per Pick Cal]
RETURN SUMX('Outbound Delivery', 'Outbound Delivery'[Total Actual] * PiecesPerPickCal)

[Justas4478]

@bhanu_gautam your solution is a bit faster, but it return wrong results.
I dont know why but results it gives back are aproximatly two times bigger than original results.

[Justas4478]

@bhanu_gautam I just checked and it is not actual results that are wrong but the totals are wrong.