So this may be a simple question...
So I am displaying the table on the left and i'm needing to create a calculated measure that multiplies ("Price" x "Shipped Quantity"), There is a measure that calculates the sum(Shipped Quantity) and I have used this in my measure which is giving me incorrect values because that measure takes into account the 2 transactions from the table on the right. I have used the measure SUM(Shipped Quantity) because it is a measure so I am able to use it in my calculation and get a total without having to filter for a specific value.
I want to multiply by 'Shipped Quantity' instead (which is a column, so I cannot just access it directly in my measure without wrapping it in a aggregated function). I have tried using 'Selectedvalue' but I also need to calculate a 2nd measure to get the total of 'TEST' (which is the calculated measure). so this is what i need help with if anybody can suggest something
TEST = Calculate ( [Price] * SelectedValue('Table'[Shipped Quantity] )
This return me the correct value but does not give me a total (is there another way to multiply/use the column in my expression)
TEST Total = SUMX( 'Table',[TEST] )
When doing this calculation to get the total it will return me the total for the table on the right (8520) but I'm wanting the total for the table on the Left (4260)
Hi, I don't know the base table in totality, but you have the following cases:
a) either the above are duplicate shipping lines (the same thing that for whatever reason appears twice in your table). Then the ID= 1 is correct und you can produce a distinct table that omits duplicate lines and then you are fine with the normal sumx expression.Or you keep the two lines and divide the result by the number of lines of this very ID
b) or the table has not the right ID. This means there should be an ID per line which is the not the one shown, rather some combination of several fields that makes the line unique
If these are really shipping orders with shipping lines, the combination of shipment number and shipping line number should be unique. If not existing you can always number the lines per shipment and create such an ID.
Hi, not entirely sure if this is what you are searching:
the problem is when i do the sum its giving me the sum for both trnasactions of the same ID and its incorrect
Hi @Euro0681 ,
Please follow these steps:
(1) Create new measure
TEST = MAX('Table'[Price])*MAX('Table'[Shipped Quantity]) TEST Total = SUMX( ALL('Table'),[TEST] )
If that's not what you need, provide sample files and expected output.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
for the total i'm still having the same issue where the total is doubling becuase it's registering both transactions and not giving me the total for the TEST (so my number is inflated)
Check out the changes to the Power BI Community announced at Build.
Find out more about the May 2023 update.
Visit our Data Stories Gallery and give kudos to your favorite Data Stories.