Sum MAX values of a column

0
votes

Situation:

I have a column (refund) that takes the MAX value for each day so that I don't duplicate the refunds.

refund =
var calcul = 
        CALCULATE(
            SUM(Query1[amount]),
            USERELATIONSHIP(Query1[created_at], DateTable[Date]),
            Query1[kind] = "refund",
            Query1[status] = "success"       
            )

This works for each day but the total is the max of that column

Objective:

I need this measure to calculate the net sales. I have the gross sales measure already but my refund formula doesn't work when aggregated.

What i tried (thanks to Alexis Olson):

refund =
var calcul = 
        CALCULATE(
            SUM(Query1[amount]),
            USERELATIONSHIP(Query1[created_at], DateTable[Date]),
            Query1[kind] = "refund",
            Query1[status] = "success"       
            )
    return 
        SUMX(DISTINCT(Query1[orderId]), calcul)

However the output is unexpected. It basically takes each value and multiplies it by the distinct count of order ids on that day (refund or not).

So i tried dividing it by the distinct count of order id but the same problem with the total row taking the max value occurs.

Here's the output i get using the provided solution below:

Returns column is correct but total isn't. Refund column is not showing all the values (date relationship issue) but the total works

Relationships:

Query1[created_at] DateTable[Date] (inactive)

Query1[orderDate] DateTable[Date] (active)

My returns measure:

Returns = 
CALCULATE(
    MAX(Query1[amount]),
    USERELATIONSHIP(Query1[created_at], DateTable[Date]),
    Query1[kind] = "refund",
    Query1[status] = "success"       
    )
2
powerbidax
What does your Returns measure look like? Also, is Day a table column (if so, what table)? – Alexis Olson
day is not a table column. My date column is from my DateTable:DateTable = ADDCOLUMNS( CALENDAR(DATE(2018,01,01),TODAY()-1), – Roger Steinberg

2 Answers

0
votes

You're pretty close to something that works. Try changing calcul to a summary table instead of a single value scalar like this:

refund =
VAR Summary =
    SUMMARIZE (
        Query1,
        Query1[orderId],
        "MaxValue", CALCULATE (
            MAX ( Query1[amount] ),
            USERELATIONSHIP ( Query1[created_at], DateTable[Date] ),
            Query1[kind] = "refund",
            Query1[status] = "success"
        )
    )
RETURN
    SUMX ( Summary, [MaxValue] )
0
votes

One way of fixing my issue was to use CALCULATETABLE before the summarize and then use the SUMX as suggested above by Alexis.

Returns = 
VAR sumary = 
    CALCULATETABLE(
        SUMMARIZE(
            Query1,
            Query1[orderId],
            "maxValue",CALCULATE(
                MAX(Query1[amount]),
                Query1[kind]= "refund",
                Query1[status] = "success"
            )
        ),USERELATIONSHIP(Query1[trx_date],DateTable[Date]))
    RETURN
        SUMX(sumary,[maxValue])