Forum Discussion

Heng's avatar
Heng
Frequent Visitor
3 years ago
Solved

Incorrect Total Unique Sale

Dear Community Team,
Please help to look at my dax code where I want to sum all sale by remove duplicate code that happen twice and keep only the last sales.
if i don't use SUMX the calculation is correct except the Total showing the average

however if I use sumx the subtoal and grandtotal is not correct as it sumup all duplicate values
Below is my code:

VAR MaxSaleDate=MAX(Sales[startPaymentDate]) RETURN SUMX(VALUES(Sales[startPaymentDate]), CALCULATE(AVERAGE(Sales[netPrice]),FILTER(Sales,Sales[startPaymentDate]=MaxSaleDate)))

Need Help
  • selimovd's avatar
    selimovd
    3 years ago

    Hey Heng ,

     

    I don't understand why you save the date in a variable and iterate over the net price.

    Does that really make sense? You didn't give more details about that.

     

    From my first impression, I would iterate over the unit type. So try the following formula:

    Sum Measure =
VAR MaxSaleDate =
    MAX ( Sales[startPaymentDate] )
RETURN
    SUMX (
        VALUES ( Sales[Unit Types] ),
        CALCULATE (
            AVERAGE ( Sales[netPrice] ),
            FILTER (
                Sales,
                Sales[startPaymentDate] = MaxSaleDate
            )
        )
    )

     

    Can you try if the following measure is working? I think the filter context should provide a viable solution:

    Sale = SUM( Sales[netPrice] )

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

    Best regards
    Denis

    Blog: WhatTheFact.bi
    Follow me: twitter.com/DenSelimovic

2 Replies

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    3 years ago

    Hey Heng ,

     

    I don't understand why you save the date in a variable and iterate over the net price.

    Does that really make sense? You didn't give more details about that.

     

    From my first impression, I would iterate over the unit type. So try the following formula:

    Sum Measure =
VAR MaxSaleDate =
    MAX ( Sales[startPaymentDate] )
RETURN
    SUMX (
        VALUES ( Sales[Unit Types] ),
        CALCULATE (
            AVERAGE ( Sales[netPrice] ),
            FILTER (
                Sales,
                Sales[startPaymentDate] = MaxSaleDate
            )
        )
    )

     

    Can you try if the following measure is working? I think the filter context should provide a viable solution:

    Sale = SUM( Sales[netPrice] )

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

    Best regards
    Denis

    Blog: WhatTheFact.bi
    Follow me: twitter.com/DenSelimovic

  • Heng's avatar
    Heng
    Frequent Visitor
    3 years ago

    Hi  Selimoved,

    Thanks for your help.
    I am not really good at DAX and I already try as you propose above the total sum is not correct
    pls see my attach pic model.  pls help to suggest the best way to write dax for this solution.

    =VAR MaxSaleDate=MAX(Sales[startPaymentDate])

     

    RETURN

    SUMX(VALUES('Properties'[code]),

    CALCULATE(AVERAGE(Sales[netPrice]),FILTER(Sales,Sales[startPaymentDate]=MaxSaleDate)))