SUMX not totaling correctly (Context Transition not applying?)

[jwin2424]

I am pulling my hair out on this one. I have transactional data for orders with numerous rows per order. I want a measure that returns orders over $500,000, but I need to group the data first by the order #. 

Here is my measure

OVER 500000 =

SUMX(

    FILTER(

     SUMMARIZE(

        'Revenue Source',

        'Revenue Source'[Order No],

        "Sales",

        CALCULATE(SUM('Revenue Source'[Master Net USD]))

     ), [Sales] > 500000

    ), [Sales]

)

Row by Row, this works, but the totals are not lining up

Some order #'s are blank, and I have tried filtering out the blanks. Again, it works at the row level, but not at the total. I have also tried GROUPBY. I have tried numerous approaches that result in the correct row level total, but not the grand total. I don't know what I am doing wrong. 

[DataNinja777]

Hi @jwin2424 ,

The issue with totals not aligning arises because DAX evaluates grand totals differently from row-level calculations. While your measure works correctly at the row level, the grand total does not automatically reapply the same logic, leading to discrepancies. To address this, you can create a summarized table grouped by Order No, filter the grouped data to exclude orders below the threshold and blank Order No, and explicitly calculate the grand total using these filtered results. Introducing a variable for the threshold value improves maintainability, allowing changes to be made in one place if the threshold changes in the future.

Here’s the updated measure with the threshold defined as a variable:

OVER 500000 = 
VAR SalesThreshold = 500000
VAR GroupedOrders =
    SUMMARIZE(
        'Revenue Source',
        'Revenue Source'[Order No],
        "TotalSales", CALCULATE(SUM('Revenue Source'[Master Net USD]))
    )
VAR FilteredOrders =
    FILTER(
        GroupedOrders,
        NOT ISBLANK([Order No]) && [TotalSales] > SalesThreshold
    )
RETURN
    SUMX(FilteredOrders, [TotalSales])

In this measure, the SalesThreshold variable sets the threshold for orders, ensuring you only need to update this value if it changes in the future. The SUMMARIZE function groups data by Order No and calculates total sales for each order. The FILTER function then removes rows where the Order No is blank or the total sales are less than the threshold. Finally, SUMX ensures that the correct total is calculated by summing the filtered sales. This approach explicitly calculates the grand total, ensuring consistency between row-level results and the total. By introducing the SalesThreshold variable, the formula is not only accurate but also easier to maintain and update.

Best regards,

[jwin2424]

I am running into the same issue. 

Anytime I use SUMX, it itterates. The [Total Sales] is using the CALCULATE function, so I am not sure why it is not properally summing. 

[johnt75 · Accepted solution]

I'm not certain, but this could be due to clustering, which is why you should never use SUMMARIZE to create calculated columns, always use ADDCOLUMNS

Try

OVER 500000 =
VAR SalesThreshold = 500000
VAR GroupedOrders =
    ADDCOLUMNS (
        SUMMARIZE ( 'Revenue Source', 'Revenue Source'[Order No] ),
        "TotalSales", CALCULATE ( SUM ( 'Revenue Source'[Master Net USD] ) )
    )
VAR FilteredOrders =
    FILTER (
        GroupedOrders,
        NOT ISBLANK ( [Order No] )
            && [TotalSales] > SalesThreshold
    )
RETURN
    SUMX ( FilteredOrders, [TotalSales] )

For a detailed explanation of why to use ADDCOLUMNS, see https://www.sqlbi.com/articles/all-the-secrets-of-summarize/ 

[Looking4PBICont]

Hello,

I am experiencing a similar problem but cannot nail down where my change needs to occur. I know there is a context transition I need to account for. I have a simple model with 3 tables: Dates  , relates to Daily_Totals table (1 to many) based on dates field, which relates to  Table3 (many to 1) based on ProjectName as the key.

FlatCost=100

Total Cost= C1+C2+C3

C1=Sum DailyTotals.Sales  where DailyTotals.Category="a"

C2=Sum DailyTotals.Sales  where DailyTotals.Category="b"

C3= Sum DailyTotals.Sales  where DailyTotals.Category not in "a","b"

I have 2 visuals; 1 table visual containing the MonthYear on the xAxis and   the field Table3.ShortDesc on the Y axis. The other visual is a card.

UtilizationRate=for each Table3.ShortDesc and MonthYear combination sum c3 as a % of monthly total  --

I know this is not the correct Dax notation but on a month by month basis,

If C1<FlatCost, then C1 where field Table3.ShortDesc="x" ,

if C1>FlatCost and shortDescription="x", then Flatcost +(C1-Flatcost)*utilizationRate,

if C1>FlatCost and shortDescription<>"x", then  (C1-Flatcost)*utilizationRate,

else blank()

My row valuues are correct, my totals are not. Here is my measure.

---

----

 var util= divide (CALCULATE(SUM(Daily_Totals[total_daily_cost]),  NOT Daily_Totals[service_description] IN  {"x","y"}),

                CALCULATE(SUM(Daily_Totals[total_daily_cost]),  NOT Daily_Totals[service_description] IN  {"x","y"},all(Directorate_Mapping)))          

var flatcost=11895

 var sup1 = CALCULATE(SUM(Daily_Totals[total_daily_cost]), Daily_Totals[service_description] = "x",REMOVEFILTERS(Directorate_Mapping[Directorate]))

RETURN  

IF( sup1-flatcost<0&&SELECTEDVALUE(Directorate_Mapping[Directorate])="SpecificName",  sup1,

 IF( sup1-flatcost>0&&SELECTEDVALUE(Directorate_Mapping[Directorate])="SpecificName",   flatcost +(sup1-flatcost)*util  ,

 IF( sup1-flatcost>0&&SELECTEDVALUE(Directorate_Mapping[Directorate])<>"SpecificName",   (sup1-flatcost)* util )))

[johnt75]

You probably need to wrap the entire code of the measure inside a SUMX over the column you are using in the visual.

[Looking4PBICont]

I was thinking i needed to create a summarization table and then use my sumx over top of that . is that on the correct path?

[johnt75]

That's certainly an option, but only really necessary if you are using more than 1 column in the visual. If you are only using 1 column then you can do a SUMX( VALUES( 'table'[column]), ... )

[Looking4PBICont]

ok good to hear i was going down the right path. my one visual is a table with a date(YearMonth) + "Category" on the opposing axis. while the other is card visual. id like to have the same measure work in both. that being said, is it correct that because the two visuals have different fields on them they are subject to different context?

To your point, I am using summarize here as part of an attempt to total by month where service_description=y, regardless of the Category which it does return as expected. 

var t1=SUMx(SUMMARIZE(Dates,Dates[YearMonth]),

        CALCULATE(SUM(Daily_Totals[total_daily_cost]),Daily_Totals[service_description]="Y" ,all(Table[Category])) )

when monthly total exceeds a given number "t",

if no, assign monthly total to where  category= "z",

if yes and catogry="z", (monthlyTotal-t)*Derived% +t

else (monthlyTotal-t)*Derived% from seperate monthly calculation (taking into consideration the category across a given month

That being said, would your suggesstion still be the same?

[johnt75]

Yes, they are subject to different contexts, but you will be able to use the same measure. You will need to produce a summarized table and then iterate over that, something like

Total =
VAR BaseTable =
    SUMMARIZE ( Sales, 'Date'[Year month], 'Product'[Category] )
VAR Result =
    SUMX ( BasedTable, [My Measure] )
RETURN
    Result

Where Sales is your fact table and Date and Product are dimension tables.

[Looking4PBICont]

i edited my last comment, should have done it in the string instead. but again here is the one variable ive created for the monthly cost across all categories. I then pulled in the cost from the daily Totals table. Does anything look off there?

var t1=SUMx(SUMMARIZE(Dates,Dates[YearMonth]),    CALCULATE(SUM(Daily_Totals[total_daily_cost]),Daily_Totals[service_description]="Y" ,all(Table[Category])) )

I have played around with the subtration a good bit as well im guessing its because of my row context but im curious to know whether my if statement would need to be reworked after I get my context problem changed.

[johnt75]

That variable looks fine, but if the rest of the calculation, including the if logic, also needs to be done in a monthly context then you would need to do the entire calculation inside the SUMX over the year month, like

Monthly measure =
SUMX (
    SUMMARIZE ( Dates, Dates[YearMonth] ),
    VAR t1 =
        CALCULATE (
            SUM ( Daily_Totals[total_daily_cost] ),
            Daily_Totals[service_description] = "Y",
            ALL ( Table[Category] )
        )
    VAR t2 = [Other measure]
    VAR Result =
        IF ( t1 < t2, t2, t1 )
    RETURN
        Result
)

So you have one outer iteration of the months and you do all your calculations inside that iterator and sum up the result.

[Looking4PBICont]

this is all very helpful. im not sure but to your comment about "only in a monthly context"..

i believe there are two different context at play here, one in the monthYear and another in the monthYear+category.

In the picture below I notated in red whether the calculation needed to be in which context.

starting from the top, going downwards in order, "utilization" drives the "utilization % of total" per monthYear and by category. Regardless of the category "support" should be group by monthYear.

Then multiply those values together conditionally. Does that make sense?

[johnt75]

So for the Support you are fine iterating over the year month, but for everything else you would need to summarize by both year month and group name, e.g.

By month & category =
SUMX (
    SUMMARIZE ( Daily_Totals, 'Date'[year month], 'Groups'[Group name] ),
    [some measure]
)

[Looking4PBICont]

Do you have an example where the two summarized tables get multiplied together and have different grains? ie  i have a Yearmonth to account for and a calculation on one table but i need to take the calculation  and multiply it against another table that also has the yearmonth in addition to the "category".

I also tried to pull in fields from 3 different tables to create a summary table but it is not letting me. Would that be due to my model?

[Looking4PBICont]

@johnt75  much appreciated once again. I got my measure to work by adding a few lines using isfiltered. my totals and field values are now returning as  expected except within one specific situation. I have a drill through into another page where there is a grouping of organizations   by a field called "office". when i drill through by office it does not return the same values+totals if I just clicked on a singular party of the group/office. It returns blank().This is what the measure currently looks like. thoughts? 

[johnt75]

You're using SUMMARIZE to create a calculated column, [TotalSupportCost]. That's not best practice and can cause problems, but I don't know if it's the issue here. Rather than using SUMMARIZE to create a calculated column, always use ADDCOLUMNS. You can nest ADDCOLUMNS within one another, so the outer one can refer to columns generated by the inner one, so it would be something like

ADDCOLUMNS(
    ASDDCOLUMNS(
        SUMMARIZE ( ... ),
        "@col1", [Some measure]
    ),
    "@col2", [@col1] * 10
)

Aside from that all I can offer is some general advice on debugging measures.

Use performance analyzer to get a copy of the query used for the visual which is displaying the measure and run it in DAX query view. If you have one version which works and one which doesn't then run them both in DAX query view and look to see what the differences in filters are.

You can also use DAX query view to define the measure, and dependent measures, which is causing the problem. Copy the definitions into the same query views where you have the DAX from performance analyzer so they're all in the same place. You can now edit the measure just for that query so you can make changes to debug it.

I find it useful to return COUNTROWS of the table variables I generate to make sure they are generating the sort of numbers I would expect. You can do this successively for each of your variables.

It can also be useful to CONCATENATEX over the table variables to show what the values of a particular column are. UNICHAR(10) is a newline character which you can use as a delimiter.

Hope this helps.

[jwin2424]

Ah ha! I figured it out! I am going to accept this as a solution. Both your solutions worked, but addcolumns is preferable. After playing around with Excel, I see the error. The calculation is itterating on one more factor - the year. A particular order can be booked in 2023 and then edited in 2024. The 2023 value could be $300,000 and the 2024 value could be $400,000, making the order go over $500,000. So the calculation is grouping that order and summing it at the bottom. However, the context of the visual is filtering the year. It won't appear at all since both instances of that order are under $500,000 when broken out by the year the revenue landed. I had to add the year into the summarized table.

Thank you for the help!

[jwin2424]

I have tried the addcolumns as well. And I have tried groupby. I still get the same results. I decided to take a few steps here. I removed the connections on the tables so I am using a single table. I still received the same results. So I went into the Excel file and found something interesting. Any explination of this? 

EDIT: I hid all the rows when I removed the year, that is why column E for excel doesnt calculate up to the total. The rest of the orders are hidden. I did that so you can see the totals side by side

[govind_021]

Hey @jwin2424 
Please use this measure

OVER 500000 =
VAR SalesThreshold = 500000
RETURN
CALCULATE(
SUM('Revenue Source'[Master Net USD]),
FILTER(
'Revenue Source',
NOT ISBLANK('Revenue Source'[Order No]) &&
CALCULATE(SUM('Revenue Source'[Master Net USD])) > SalesThreshold
)
)

[jwin2424]

This is not calculating orders > 500k, it is calculating rows over 500k.