Hello Community,
I created the two measures below, and thought they were working fine. In reviewing my data, I noticed something wrong with how I've done this that I'm sure is easy to fix...just not sure how. The problem is that Orders can have multiple lines, and these measures are not aggregating the total order value of all the lines - see explanation below.
I need to sum the value of orders in two distinct groups, and I need to count the "distinct" number of orders in each of those groups (in other words, count the number of distinct Order #'s within each group). Orders that have a value of $75,000 or more. Orders less than $75000.
Count of More Than 75k = CALCULATE(DISTINCTCOUNT('Flu Shipped'[Order]),'Flu Shipped'[Net Price]>=75000)
Count of Less Than 75k = CALCULATE(DISTINCTCOUNT('Flu Shipped'[Order]),'Flu Shipped'[Net Price]<75000)
These measure I thought were working fine, until I realized that they were counting the value of each line
in an order, rather than aggregate total of the order.
Example: Order # 2367A might have 4 lines to it. If I put my measures in table, it was counting
Order 2367A twice: Once for lines 1,3, and 4 because they totaled < 75k. And once for line 2 because
it totaled over 75k. The count I need for this order would be "1" because in aggreagate, the total
order value is over 75k.
Order # Line $ Value
2367A 1 20,000
2367A 2 80,000
2367A 3 10,000
2367A 4 15,000
Thanks in advance for any help!
