DAX Aggregation Total Challenge

[Kenny123]

Hello Guys,

I have a white hair challenge that I've been trying to solve for at least a week without any success. The requirement is fairly simple, basically trying to produce the correct totals based on amounts that are rolled up to the parent .

Screenshot shown below attempts to highlight the totals issue and also how the amounts get rolled up in the detail rows.

There is a single filter on FY = 2019. When rolled up, the child projects shows 0 as amount which is expected as the dollars are attributed at the parent level.

I'm really hoping someone can provide some guidance around how to solve this one as it's driving me and my colleage up the wall 😞

Here is the DAX code currently used for the totals:

SUMX(
                    VALUES('Table'[ProjectID]), 
                    CALCULATE([Total Amount by Project],  'Table'[AllocationID] IN {"_Not Applicable", ""})
                )

                +

                SUMX(
                    VALUES('Table'[AllocationID]),
                    CALCULATE([Total Amount by Project],  NOT('Table'[AllocationID] IN {"_Not Applicable", ""}))
                )  

Also attached the PIBX file used to demo the issue.

Sample-Aggregation.Totals.Challenge.pbix 

Any assistance would be greatly appreciated 🙂

[BrianConnelly]

Use the ISINSCOPE....

Return IF(ISINSCOPE('Table'[ProjectID]),Decision,CALCULATE(sum('Table'[Amount])))

Your Measure...

Total Amount (Aggregated) = 

	VAR Result = 

        IF(MAX('Table'[AllocationID]) IN {"_Not Applicable", ""},

            [Total Amount by Project],

            IF(LEFT(MAX('Table'[ProjectID])) =  "A",

                // Aggreagate for Allocations Only
                
                CALCULATE(
                    SUM('Table'[Amount]),
                    FILTER(
                        ALLEXCEPT('Table', 'Table'[FY]),
                        'Table'[AllocationID] = MAX('Table'[AllocationID]) 
                    )
                )
                ,
                
                // Child Projects return nothing as they already rolled up to Allocation
                0
                
            )
        )


	
	VAR Decision = 
        IF(HASONEFILTER('Table'[ProjectID]),

            // Detailed Rows
                Result,


            // Totals Row
            
               SUMX(
                    VALUES('Table'[ProjectID]), 
                    CALCULATE([Total Amount by Project],  'Table'[AllocationID] IN {"_Not Applicable", ""})
                )

                +

                SUMX(
                    VALUES('Table'[AllocationID]),
                    CALCULATE([Total Amount by Project],  NOT('Table'[AllocationID] IN {"_Not Applicable", ""}))
                )  
        )

	Return IF(ISINSCOPE('Table'[ProjectID]),Decision,CALCULATE(sum('Table'[Amount])))

[Kenny123]

Thank you heaps, was so excited until it return a dud when I filtered on ProjectID. Is there a way to produce the correct total if filter is applied on FY or ProjectID?

[BrianConnelly]

Im not sure what value you are expecting here.... is this the result you expect?

Return IF(ISINSCOPE('Table'[ProjectID]),Decision,CALCULATE(sum('Table'[Amount]),ALL('Table'[FY],'Table'[ProjectID])))

[Kenny123]

I'm expecting $1660 as the total when filtered on AID01 and AID02. The new suggestion produced $2680 so a tad off still.

[BrianConnelly]

Is the total wrong or the rows with the FY and Project ID filters?

[Kenny123]

The total is wrong. Because the measure it meant to represent rolled up total, AID01 should include the amounts of AID01 + PID01 and PID02. So when we filter on ProjectID AID01 for example, it should be $1530. 

When filtered on AID01 and AID02, the grand total (rolled up) should be $1660. It's is a bit hard to explain but essentially, the details amount is correct, just not reflecting on the total row.

[BrianConnelly · Accepted solution]

Modify your code with the following...

VAR ProjectID = SELECTEDVALUE('Table'[ProjectID],"ALL")
VAR sumTotal = IF(ProjectID = "ALL", 
CALCULATE(sum('Table'[Amount]),FILTER(ALLEXCEPT('Table','Table'[FY]),'Table'[AllocationID] IN VALUES('Table'[ProjectID])))
, CALCULATE(sum('Table'[Amount]),FILTER(ALLEXCEPT('Table','Table'[FY]),'Table'[AllocationID] = ProjectID))
)

Return IF(ISINSCOPE('Table'[ProjectID]),Decision,sumTotal)

Your full code...

Total Amount (Aggregated) = 

	VAR Result = 

        IF(MAX('Table'[AllocationID]) IN {"_Not Applicable", ""},

            [Total Amount by Project],

            IF(LEFT(MAX('Table'[ProjectID])) =  "A",

                // Aggreagate for Allocations Only
                
                CALCULATE(
                    SUM('Table'[Amount]),
                    FILTER(
                        ALLEXCEPT('Table', 'Table'[FY]),
                        'Table'[AllocationID] = MAX('Table'[AllocationID]) 
                    )
                )
                ,
                
                // Child Projects return nothing as they already rolled up to Allocation
                0
                
            )
        )


	
	VAR Decision = 
        IF(HASONEFILTER('Table'[ProjectID]),

            // Detailed Rows
                Result,


            // Totals Row
            
               SUMX(
                    VALUES('Table'[ProjectID]), 
                    CALCULATE([Total Amount by Project],  'Table'[AllocationID] IN {"_Not Applicable", ""})
                )

                +

                SUMX(
                    VALUES('Table'[AllocationID]),
                    CALCULATE([Total Amount by Project],  NOT('Table'[AllocationID] IN {"_Not Applicable", ""}))
                )  
        )
VAR ProjectID = SELECTEDVALUE('Table'[ProjectID],"ALL")
VAR sumTotal = IF(ProjectID = "ALL", 
CALCULATE(sum('Table'[Amount]),FILTER(ALLEXCEPT('Table','Table'[FY]),'Table'[AllocationID] IN VALUES('Table'[ProjectID])))
, CALCULATE(sum('Table'[Amount]),FILTER(ALLEXCEPT('Table','Table'[FY]),'Table'[AllocationID] = ProjectID))
)

Return IF(ISINSCOPE('Table'[ProjectID]),Decision,sumTotal)

[Kenny123]

Nevermind, I found the solution after tweaking your logic. It's working now!

    VAR ProjectID = SELECTEDVALUE('Table'[ProjectID],"ALL")
    VAR sumTotal = 
        IF(ProjectID = "ALL", 
            CALCULATE(sum('Table'[Amount]),FILTER(ALLEXCEPT('Table','Table'[FY]),IF('Table'[AllocationID] IN {"_Not Applicable", ""}, 'Table'[ProjectID], 'Table'[AllocationID]) IN VALUES('Table'[ProjectID])))
            , CALCULATE(sum('Table'[Amount]),FILTER(ALLEXCEPT('Table','Table'[FY]),'Table'[AllocationID] = ProjectID))
        )

Above is the updated. Needed to account for blank (and 'Not Appliable') AllocationID 

[Kenny123]

Thanks Brian, much appreciate you lending a hand.

The latest logic does result in expected total when filter is applied. But unfortunately, once the ProjectID filter is removed, it produces a strange total. I got a feeling it just need a minor tweak and we're there

[BrianConnelly]

Because nothing was done for the allocationIDs for the Not Applicable or Blanks.  It would need to be tweaked to handle them.

VAR ProjectID = SELECTEDVALUE('Table'[ProjectID],"ALL")
VAR sumTotal = IF(ProjectID = "ALL", 
CALCULATE(sum('Table'[Amount]),FILTER(ALLEXCEPT('Table','Table'[FY]),OR('Table'[AllocationID] IN VALUES('Table'[ProjectID]),AND(NOT('Table'[AllocationID] IN VALUES('Table'[ProjectID])),'Table'[ProjectID] IN VALUES('Table'[ProjectID])))))
, CALCULATE(sum('Table'[Amount]),FILTER(ALLEXCEPT('Table','Table'[FY]),'Table'[AllocationID] = ProjectID))
)

Return IF(ISINSCOPE('Table'[ProjectID]),Decision,sumTotal)