DAX Aggregation Total Challenge

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 ...

Need Help

BrianConnelly

3 years ago

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)

BrianConnelly

Resolver III

3 years ago

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

Kenny123

Frequent Visitor

3 years ago

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

Resolver III

3 years ago

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
Frequent Visitor
3 years ago
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
  Resolver III
  3 years ago
  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)

Kenny123

Frequent Visitor

3 years ago

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

Forum Discussion

DAX Aggregation Total Challenge

Related content

getting challenges with data science

Totals are wrong in Aggregation

Totals Aggregation Issue

No CACULATE challenge

Aggregate to show Total not individual