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

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's avatar
Kenny123
Frequent Visitor
3 years ago

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

 

 

  • BrianConnelly's avatar
    BrianConnelly
    Resolver III
    3 years ago

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

     

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