Forum Discussion

Kenny123's avatar
Kenny123
Frequent Visitor
3 years ago
Solved

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's avatar
    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's avatar
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's avatar
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's avatar
    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)