Get volume (multiplication)

[brinky]

Hello,

I'm trying to get the volume sales as follow however I'm getting the wrong values.

I'm using measure 2 to get the sales qty then I'm using measure 4 to get volume however volume values are wrong 🤔 combined sales tables and volume tables are connceted.

Any idea how I can solve such issue? I was trying to get the volume sales with measure 2 using related function but didn't manage.

[v-rzhou-msft]

Hi @brinky ,

I think your [Measure 4] is based on [Measure 2] and related

I create a sample to have a test.

CombinedSales:

VolumTable_1:

I created a relationship between two tables by Category.

Measures are the same, it works well.

Firstly, please check whether [Measure 2] is correct.  You can add a calculated column by related function to get data from [Litres] in CombinedSales.

C_Litries = RELATED(VolumTable_1[Litres])

Or create a measure.

C_Litries = calculated(sum(VolumTable_1[Litres]),Filter(VolumTable_1,VolumTable_1[...] = max(CombinedSales[...])))

 And check whether [Litres]  part is correct.

Best Regards,
Rico Zhou

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

[brinky]

@v-rzhou-msft

Thanks for your reply & sample data.

I have these 2 tables connected with Code (attached), what I am after is;

if Return_Reason is 1 or blank Sum the Invoiced_QTY and multiple with Volume from the Volume table

https://brincats-my.sharepoint.com/:u:/p/info/Ecge6-_iy4tAlP-1s9c1bZ0B2shp7N1JYpi4YFCogiN-Dg?e=3RdGg...

[v-rzhou-msft · Accepted solution]

Hi @brinky ,

Try this code.

Measure 3 = 
VAR _Invoiced_QTY = 
CALCULATE(SUM(Combined_Sales[Invoiced_QTY_PU]),FILTER(Combined_Sales,Combined_Sales[Return_Reason] in {1,BLANK()}))
VAR _Volumn = CALCULATE(SUM(Volume[Volume]),FILTER(Volume,Volume[CSB_ID_Code] = MAX(Combined_Sales[CSB_ID_Code])))
RETURN
_Invoiced_QTY*_Volumn

Result is as below.

Best Regards,
Rico Zhou

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

[bcdobbs]

Hi @brinky,

It's not clear from the detail so far how your volume table comes into play as everything just references combined sales. Are you able to share a demo pbix with your structure and some data? Alternatively some dummy data from each table?

[brinky]

Hi @bcdobbs 
Sorry loaded the wrong measure I have updeted my post.

Thanks

[bcdobbs]

Could you provide some sample data from the two tables, an image of the relationship between them and a few correct values for the calculation.