How to avoid sum of duplicate values in a measure?

[TiwariNeha]

Hi Power BI Community,  

I need some urgent help. 

Issue : How to avoid sum duplicate values in a measure ..

Example: -   We have 2 dates snap_date and period_date. 

                    In the Slicer, if I choose 'ALL' snap_dates and period_date as '6/01/2020',

                    we will find the product_id = 10 is duplicated.

                    So in case of duplication 'aggregation' should not happen, only Sales amount belonging to Latest snap date                              should show.

                    In our Scenario

                    Product_ID 10 is duplicate so it should only show Sales amount for its latest snap_date  6/22/2020

                    i.e sales amount = 3000 and  should ignore 1000 Amount for date 5/22/2020.
                    Also, Producd ID 9 , should show sales amount = 300
                    AND When  user selects Both Product_ids(10 and 9) in the filter = sum should be 3300
                    but my sum is coming as 4300.

Please find the Screen shots and Excel and PBIX file links.

 Current Output: -

Required output:-

Sharing :  link for PBIX and EXCEL files

https://drive.google.com/file/d/14SBMji1NqkCwShszz8Ap6Fi5g3ACpbAi/view?usp=sharing     - PBIX

https://drive.google.com/file/d/1-ZjTkywhMso1UpEnlkzkWbrvaX2eWpj_/view?usp=sharing     - EXCEL

Please feel free to contact 8130661129 for further clarifications

[TiwariNeha]

Hi @Nathaniel_C Nathaniel, 
Thanks for your reply. The output that is coming from the suggested calculation is different from the expected solution. 

Let me explain , if we dont take the sanpdate 06/22/2020,  then for period date 06/01/2020, Sum should be 1300  for product id 9 and 10 as there are no duplicates for that particular dates.

Thanks & Regards:
Neha

[V-lianl-msft · Accepted solution]

Hi @TiwariNeha ,

Based on your description, Please create a sort measure first.

RANK = 
RANKX (
    FILTER ( ALLSELECTED ( Master ), Master[product id] = SELECTEDVALUE ( Master[product id] ) ),
    CALCULATE ( SELECTEDVALUE (Master[Snap_date]) ),
    ,
    DESC,
    DENSE
)

Measure = CALCULATE(SUM(Master[(Sales_order_history)]),FILTER(ALLSELECTED(Master),[RANK]=1))

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

[Nathaniel_C]

Hi @TiwariNeha , Not sure if I understand what you want, however try this:

Sum of Sales =
VAR _curSnapDate =
    MAX ( Master[Snap_date] )
VAR _curProductID =
    MAX ( Master[product id] )
VAR _curCustomerID =
    MAX ( Master[customer id] )
VAR _calc =
    CALCULATE (
        MAX ( Master[(Sales_order_history)] ),
        FILTER (
            ALL ( Master ),
            Master[customer id] = _curCustomerID
                && Master[product id] = _curProductID
                && Master[Snap_date] = MAX ( Master[Snap_date] )
        )
    )
VAR _maxDate =
    CALCULATE (
        MAX ( Master[Snap_date] ),
        FILTER (
            ALL ( Master ),
            Master[customer id] = _curCustomerID
                && Master[product id] = _curProductID
        )
    )
RETURN
    IF ( _maxDate = _curSnapDate, _calc )

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel