Getting rid of duplicate values being added together

[rcb0325]

Hello! 

In the table I am building, it is relating customer groups, to a sales order, to an invoiced quantity. Each customer is assigned a rebate ID of either quarterly or annual. The issue I am having is that customers that have both a quarterly and annual rebate, the invoiced quantities are being doubled. 

I am wondering if there is a way to create a new measure for the invoiced quantities, that tells Power BI to look at the Rebate ID, and if there is both a quarterly and annual rebate for that customer, then divide the invoiced quantity by 2. Below are some pictures of my issue. Thank you! 

[v-yalanwu-msft · Accepted solution]

Hi， @rcb0325 ；

if  invoiced quantity is column,you could create a measure as:

InvoicedQuantity2 =
IF (
    CALCULATE (
        DISTINCTCOUNT ( [RebateProgramID] ),
        ALLEXCEPT ( 'Table', 'Table'[CustomerNumber] )
    ) = 2,
    AVERAGE ( [InvoicedQuantity] ) * 2,
    MAX ( [InvoicedQuantity] )
)

if  invoiced quantity is measure,you could create a measure as:

InvoicedQuantity3 =
IF (
    CALCULATE (
        DISTINCTCOUNT ( [RebateProgramID] ),
        ALLEXCEPT ( 'Table', 'Table'[CustomerNumber] )
    ) = 2,
    AVERAGEX ( 'Table', [InvoicedQuantitymea] ) * 2,
    [InvoicedQuantitymea]
)

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

In the invoiced quantity measure, use the Min function instead of the Sum function.

[rcb0325]

Could the Average function be used? Or is there a reason that the Min function is the best option? 

[Ashish_Mathur]

Hi,

As long as the invoice quantity for the Customer Name remains the same, you should be able to use Max, min or average.

[lbendlin]

I think you have bigger problems than that.  Your sales amount is also doubled.

Maybe the rule should be "Don't include a quarterly row if an annual row is present" ?