Editing behaviour of (sub)totals using DAX

[SanderTK]

I am using a very simple measure that is an IF statement to determine values based on other values.

If more than 500 tickets are sold I want the value 100 returned, else if more than 400 are sold I want the value 50 else I want the value 25.

Calculating the amount of tickets sold is a stand-alone measure so the IF statement looks a bit like this:

My Measure =
IF([num tickets sold] > 500, 100,(IF[num tickets sold] > 400,50,25))

I format this in a matrix on the granularity of days.

This works perfect on days with 1 event but as soon as there are 2 events on 1 day, it gives me the sum of tickets sold of those two events (lets say 200 and 300) which would be 500, giving me a result of 100 when in fact the result should be 25+25 = 50.


(example made in Excel because it was a faster way of visualizing the issue)

I can solve this by going one level deeper and placing the events underneath the dates so that I can drill down one step further and get the right results on event level but what I cannot seem to do is get the totals right.

Because of the filter context in the totals I have the same problem as with days with multiple events but all I know what to do about this, is to hide the totals by using a IF(HASONEVALUE) statement on the day column.

What I would like to do basically is get the actual totals of the previous rows and sum them in the (sub)total field.

[bsheffer · Accepted solution]

would sumx(event_table, mymeasure) help?

[v-rzhou-msft · Accepted solution]

Hi @SanderTK ,

Here I create a sample to have a test.

Measure:

Measure = 
VAR _SUMMAZIRE =
    ADDCOLUMNS (
        SUMMARIZE (
            'Table',
            'Table'[Date],
            'Table'[Event],
            "num tickets sold", CALCULATE ( SUM ( 'Table'[num tickets sold] ) )
        ),
        "IF",
            IF ( [num tickets sold] > 500, 100, IF ( [num tickets sold] > 400, 50, 25 ) )
    )
RETURN
    SUMX ( _SUMMAZIRE, [IF] )

Result is as below.

Best Regards,
Rico Zhou

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

[SanderTK]

What I was missing was adding the tickets sold from the transactions table to the events table which I can do with the last argument of that SUMMARIZE statement. It works perfectly now, thank you so much for the help!

[bsheffer · Accepted solution]

would sumx(event_table, mymeasure) help?

[SanderTK]

I now realize that I was using the SUMX over the transactions table (which I would need to calculate the sum of tickets sold). If I had the amount of tickets sold in the events table this would've worked perfectly. Thank you for the help!

[SanderTK]

I have tried this but with no result. Tried using keep/removefilters as well but did not help.